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 Community,
A total NOOB here. I need to get a matrix visualization, with a measure ([% of TOTAL]) that changes the calculation based on what level is being shown.
My table has some [Product Categories], and those product categories have several [Product SubCategories].
For every SubCategory, i have a [VOLUME] value that I need to SUM and then divide on the Total to get the [% Of Total] measure.
But this calculation changes denominator, based on what level of row I'm showing, based on a drill down.
I need to show a table (or matrix) with the following outcome:
How can I accompish this in Power BI? thanks in advance for the help
Hi,
Volume should be a measure
Volume = SUM(Data[Quantity])
% of Total = IF(HASONEVALUE(Data[SubCategory]),[Volume]/CALCULATE([Volume],ALL(Data[SubCategory])),[Volume]/CALCULATE([Volume],ALL(Data[Category])))
Hope this helps.
HI @Ashish_Mathur,
Thank you for your answer.
I did exactly what you told me in two different scenarios:
1.- When the Values for the measure (Let's keep calling them Data[Quantity] in this example and are the values you see in the second column titled VOLUMEN in the screenshot below) are in the same table as the Dimensions (Data[Category] and Data[SubCategory] which are the values for the first column you can see in the screenshot below titled LINEA PRODUCTOS BD), it worked perfectly.
2.- Having the Values for VOLUME measure in another table (Lets call it FACT[Quantity] for this example), it returns me 1.00 for every row in the column [% of Total]
What am I doing wrong?
Hi,
There is a missing relationship. To get further help, share the link from where i can download your PBI file.
You can download my pbix file from here: https://www.dropbox.com/s/hripff2ugx7vsuy/_AG_Model.pbix?dl=0
Some things to explains the model. Since the user wants to be able to select two scenarios, based on the same filter options (Let's say [YEAR], [Q], EXERCISE/PERIOD] and some other columns), and produce a third table with the variations on those selected scenarios I had to duplicate the DATA table (kind of a fact table) to be able to filter each and then produce the third one.
Any recommendations are more than welcome.
Hi,
I am comfortably confused. Just show me one table in that PBI file, point out the incorrect number there and show me the expected result.
Sorry @Ashish_Mathur
I'm confused too... You are telling me that you're looking the correct results?
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |