cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Measure based on level of Drill Down

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: 

Annotation 2019-02-23 194134.jpg

 

How can I accompish this in Power BI? thanks in advance for the help

6 REPLIES 6
Highlighted
Super User V
Super User V

Re: Measure based on level of Drill Down

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Measure based on level of Drill Down

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]

Results.jpg

What am I doing wrong?

Highlighted
Super User V
Super User V

Re: Measure based on level of Drill Down

Hi,

 

There is a missing relationship.  To get further help, share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Measure based on level of Drill Down

Hi @Ashish_Mathur 

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.

Highlighted
Super User V
Super User V

Re: Measure based on level of Drill Down

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Measure based on level of Drill Down

Sorry @Ashish_Mathur 

I'm confused too... You are telling me that you're looking the correct results?

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors