cancel
Showing results for
Did you mean:
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:

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

6 REPLIES 6
Highlighted
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
Highlighted
Frequent Visitor

## Re: Measure based on level of Drill Down

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?

Highlighted
Super User V

## Re: Measure based on level of Drill Down

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Frequent Visitor

## Re: Measure based on level of Drill Down

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

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