Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table with a lot of data that has columns for sales in 2018 and 2019 by region and store ID.
I want to present the data for sales in a matrix with the ability to drill down from the Region to store ID and drill back up.
I want to be able to see the sales volume as a percent of the column total for 18 and 19 when I'm drilled up or down.
I want to show the percentage difference between 18 and 19.
I can achieve everything except the percentage difference between 18 and 19.
Step 1 | This sample matrix shows the sum of sales as percent of column total | ||||
but I am unable to calc the difference b/c these values don't exist in the table | |||||
REGION | SALES '18 | SALES '19 | %Change | ||
NORTH | 18% | 29% | |||
SOUTH | 32% | 40% | |||
EAST | 25% | 17% | |||
WEST | 25% | 15% |
Step 2 | Drill down on North, the 18% of sales is converted to 100% | |||
and shows the breakout for that region by store id as a percent of column total | ||||
Again, I can' t show the percentage difference | ||||
REGION | SALES '18 | SALES '19 | %Change | |
NORTH | 100% | 100% | ||
135 | 20% | 22% | ||
119 | 25% | 29% | ||
214 | 65% | 70% |
Problems: |
With the values for the sum of sales for 18 & 19 shown as % of column total, I can't simply subtract 19 from 18.
I don't have the dax calculation for the, "show value as" calc, so I can't create calculated columns and then subtract 1 from the other.
I tried: |
Calculating the first step % change by creating new calculated columns and then subtracting them.
Sales '18 = Query1[sales_18]/calculate(sum(Query1[sales_18]),ALLSELECTED())
Sales '19 = Query1[sales_19]/calculate(sum(Query1[sales_19]),ALLSELECTED())
%Change=[Sales '19]-[Sales '18]
That worked until I drilled down...
this is right | |||
REGION | SALES '18 | SALES '19 | %Change |
NORTH | 18% | 29% | 11% |
SOUTH | 32% | 40% | 8% |
EAST | 25% | 17% | -8% |
WEST | 25% | 15% | -10% |
when I drilled down it gave me this:
REGION | SALES '18 | SALES '19 | %Change |
NORTH | 18% | 29% | 11% |
135 | 7% | 8% | 1% |
119 | 8% | 10% | 2% |
214 | 3% | 11% | 8% |
I want this:
REGION | SALES '18 | SALES '19 | %Change |
NORTH | 100% | 100% | 11% |
135 | 39% | 28% | -11% |
119 | 44% | 34% | -10% |
214 | 17% | 38% | 21% |
Solved! Go to Solution.
instead of creating calculated columns i had to create measures
Hi
You can try the following:
Year 18 = divide(sum(Query1[sales_18]),calculate(sum(Query1[sales_18]),ALLSELECTED()))
Year 19 = divide(sum(Query1[sales_19]),calculate(sum(Query1[sales_19]),ALLSELECTED()))
% Change = [Year 19] - [Year 18]
Let me know if it works for you.
Tomas
No that didn't work - the calc I'm looking to replicate is what ever is behind the, "show value as a percent of column total". When I drill down in the matrix that calc works dynamically. Instead of calcing based on the total sales it looks specifically at the total for the region.
instead of creating calculated columns i had to create measures
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |