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
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.
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?
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.
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.