Dynamic measures for drilldowns

[mapko]

All,

My hierarchy is as follows:

L1: main KPI

L2: sub KPIs

Example:

KPI: Quality

Sub KPI: vendor quality; material quality; customer quality

Approach I take now is as follows:

1) create separate measures for each KPI/sub-KPI level 

2) create 2 charts; 1 chart with KPI as a dimension in which i use yet another measure that based on KPI ID will calculate and display correct %; and 1 chart wiht sub-KPIs as a dimension in which i replicate the previous measure but at the sub-KPI level

3) selections in the KPI chart nicely displays related sub-KPIs

But, I would like to move towards the drill down function but my current measures are not displaying correct numbers anymore. KPI level gets calculated correctly while sub-KPIs get defaulted to the KPI numbers.

Any idea how I can solve this?

Thanks! 

[ankitpatira]

@mapko I think for the drilldown approach if you create one measure to be used for both kpi and sub-kpi dimension and then also have a slicer visual (with field of kpi), then you will see correct numbers when you drill down from kpi to sub-kpi level based on the selection you've made for slicers.

[mapko]

@ankitpatira thank you for your response. Any chance you can show me an example of how to approach this with a DAX query? Creating another filter object does not make any changed to my drilldown chart. Below what I'm experiencing. Thanks!

[v-sihou-msft]

@mapko

In this scenario, you already have a hierarchy "KPI->Sub KPI" appeared on X-axis. But actually you also have multiple quantity measures group on "Quantity". However, both parent and child level members can only take the measures you drag into Values Pane. We can never keep the "Quantity" measure on parent level (KPI), and split it into different quantity measures when drilling to child level members (Sub KPI). It's not possible to drill down on measure level.

Regards,