Show Different Values According to the Hierarchy

[gdoss]

Hi everyone!

A friend told me that was possible but he didn't say how, that's why I'm here. I'm using a stacked bar chart and that's how it was plotted.

Axis: a 2 levels hierarchy

Value: Target 

The thing is: for each hierarchy level, there are different values and I'd like this values changed according to the hierarchy. Is there some way to do this? by DAX or any other PBI resource...

[v-lili6-msft]

hi, @gdoss 

I think you may try to use ISINSCOPE to get the right hierarchy level in DAX

Please refer to this blog:

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Best Regards,

Lin

[mussaenda]

Hi @gdoss ,

provide sample date and expected output to help you better.

Thank you

[gdoss]

Hi @mussaenda!

The data are confidential, but I've attached a Sample and a .xlsx where the same idea may be applied. 

There are 2 databases. I wish to plot on a stacked bar chart an acompaniment of the target %. 

My axis hierarchy is Country > State. There are different values for the %target of the countries and states and I expect the correct value to be shown as I drilldown. In the .xlsx file, the columns % Target State / % Target Country shows the expected output.

[gdoss]

---

@gdoss wrote:

Hi @mussaenda!

The data are confidential, but I've attached a Sample and a .xlsx where the same idea may be applied. 

There are 2 databases. I wish to plot on a stacked bar chart an acompaniment of the target %. 

My axis hierarchy is Country > State. There are different values for the %target of the countries and states and I expect the correct value to be shown as I drilldown. In the .xlsx file, the columns % Target State / % Target Country shows the expected output.

---

Couldn't send the files here (don't know why) but the files are in this link https://forumpowerbi.com/viewtopic.php?f=8&t=2722&p=6018#p6018 (is a brazilian forum for Power BI)

[mussaenda]

[gdoss]

@mussaenda great!

About states ok! That's it. But what happens when I drill up? The sum will be just the sum of the states inside that country hierarchy, or are they a percentage of the Country - Target (that would be the right)?

thanks!

[mussaenda]

hi @gdoss ,

Have you tried?

The sum will show according to what you put inside the visual.

Try it and you can also answer the question by yourself and see if it is showing right values.

Do not forget to close the thread if your question has been solved.

[gdoss · Accepted solution]

Hi @mussaenda!

I've tried this way but it didn't work. Drilling up, the visual just summed all th results above the hierarchy. But I used ISFILTERED and it worked! 

This was the measure: 

% Meta = IF(ISFILTERED('Target - State'[State]);CALCULATE(SUM('Target - State'[Done])/SUM('Target - State'[Target]);ALLEXCEPT('Target - State';'Target - State'[State]));IF(ISFILTERED('Target - State'[Country]);CALCULATE(SUM('Target - State'[Done])/AVERAGE('Target - State'[Target Country]);ALLEXCEPT('Target - State';'Target - State'[Country]));""))