Hi Mynda,
I'm trying to wrap my head around this one. Can you give a hand? 
I have one sheet with revenue per month per dentist clinic. Another sheet with 15 different accounts with costs (rent, consumables, electricity, etc) per month for each clinic. We talk about over 1000 clinics in total and three years of cost data. My aim is to compare the costs clinics accumulate. I do this by comparing the cost per account as a percentage of each clinics' revenue, then make charts comparing all the clinics' "cost-percentage" using slicers to make it manageable. Slicers will be used to cut the data by county, city and year
My goal is to be able to add data later on in separate workbook files exported from Qlik and automatically update the charts via the PowerQuery macro, churning through the folder files as they are added.
Question: should I (A) group the cost per clinics to a yearly cost in PowerQuery, do the same for the revenue and later compare cost in PowerPivot using Calculated Columns?
Or (B) is it better to use Measure for this and perhaps skip the grouping of data to yearly sums in Query? I understand how to use Calculated Columns for this, but I don't know if it's better to try and learn to use the Dax formula Calculate (and anothers) as a Measure instead for better performance?
Best regards,
Jan , Sweden
Hi Mynda,
I think I got some things wrong in my original post.
I tested the group-functionality in PowerQuery. I tried to group costs per year for one clinic. It's doesn't work as I thought. I realized that the data grouping function I was thinking of belonged to PowerPivot and not Query. Perhaps Powerpivot can handle the data after all? I saw that in total it's not more than 900.000 rows. I don't want the presentation to be unresponsive on a laptop if I can avoid it.
/Jan
Hi Jan,
It's difficult to visualise completely, but I'd say if you can aggregate your data to yearly totals in Power Query at the level of detail you need for your analysis; clinic, expense account etc. then this will definitely improve performance, especially if you'll be adding data ongoing.
If you're stuck getting Power Query to group the data the way you envisage, please share an Excel file with an extract of your data and the desired output and I can help.
Mynda
Hi Mynda,
Thanks for your quick reply! The grouping of data wasn't that much of a performance issue. I made some quick charts without grouping data in Query. It worked fine. The problem for me is getting a number to compare clinics from two different tables. The number or "percentage" I'm looking for is the result of this operation: dividing the cost of each account per clinic with the revenue for the same period (year) of that clinic.
How do I do that in the best way? I can't keep all the relevant information in one table because the revenue is imported from a different data source, separate from costs. See my attached example file.
Regards,
Jan
Hi Jan,
What you have is two fact tables; one for revenue and one for costs. Please watch sessions 6.07 and 6.08 of the Power Pivot course for how your model needs to be structured.
You will need date and clinic number dimension tables (at least) to relate the two fact tables to. Then you'll be able to add measures to calculate the percentages you're referring to. e.g.
Costs: =SUM([Cost])
Revenue: =SUM([Revenue])
Revenue % of Costs: =DIVIDE(Revenue, Costs)
Mynda
