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 all!
I've been wrestling with this matrix problem for a while. There's an interesting business requirement to have a table that has the following:
In the matrix visualization, there are several layers of drilldown, as follows:
Category
Subcategory
Promotion Group
I am calculating Current Sales and Prior Sales for these 3 attributes. There are three slicers; one for a "Quarter", "Period", and Customer Name which all filter the matrix Prior Sales dollars.
Current Sales is NOT impacted by the user selection of Quarter (forced to Current Quarter by my CALCULATE function, as seen in the below calculation)
Current Sales:
CALCULATE(SUM('Total Historical'[Sales $]),'Total Historical'[Category] = "Trade Cty" || 'Total Historical'[Category]= "Shopper Cty", 'Total Historical'[Quarter] = "Current Quarter"
The Sales are CORRECT in Subcategory, but some Promotion Group names are missing depending on "Quarter" or "Period" selected. I do not want this.
Here's what I'm seeing:
Category Name | Current Quarter Sales
Coupons -700K
+Subcategory 1 -400K
+Promo Group 1 -20K
+Promo Group 2 -30K
+Promo Group 3 -15K
+Subcategory 2 -300K
+Promo Group 4 -100K
+Promo Group 5 -200K
Important information:
1. Category Sales and Subcategory sales are CORRECT. But there are missing Promotion Groups in one Subcategory and the rollup doesn't make sense
2. The issue is seen in the Current Quarter Sales category when I select a historical quarter. If I select "Current Quarter" from the slicer, I see all Promotion Groups as expected, and the rollups all work correctly.
Question:
There is no data model; there is only 1 table used, with current quarters and historical quarters. How can I force the matrix table to display ALL Promotion Groups, regardless of slicer quarter selected, but still display correct Current Sales?
Thanks so much for your time - I'll be sure to grant kudos to the genius who solves it!
Solved! Go to Solution.
Hi @TMLewis ,
Create a dimension table as below:
Dimension Table = VALUES(Sheet1[Quarter])
Then create a measure as below:
_Prior Sales = FORMAT(DIVIDE(CALCULATE(SUM('Sheet1'[Sales]) ,FILTER(Sheet1,'Sheet1'[Quarter]=SELECTEDVALUE('Dimension Table'[Quarter]))),1000000),"0.0M")
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@TMLewis ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
https://1drv.ms/u/s!Av5L0aIROExnkjbEENtSaeVqwlai?e=f9rqrt
Thanks @amitchandak - I can't directly link a file to the Forum (I'm guessing because I'm new?) but here's a PBIX file with the general issue.
When swapping between Quarter: "Current Version" and Quarter: P9, different promotions will appear. I want all of the promotions to appear, regardless of Quarter choice.
Thanks so much!
TML
Hi @TMLewis ,
Create a dimension table as below:
Dimension Table = VALUES(Sheet1[Quarter])
Then create a measure as below:
_Prior Sales = FORMAT(DIVIDE(CALCULATE(SUM('Sheet1'[Sales]) ,FILTER(Sheet1,'Sheet1'[Quarter]=SELECTEDVALUE('Dimension Table'[Quarter]))),1000000),"0.0M")
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |