Data filtering problem with slicer

[Anonymous]

Hi everyone,

I have a data table:

I need to perform KPI for Organization, Team and every employee, KPI of Team is the data of the employees in the team, KPI of Organization is the data of all employees. The result I want is a diagram showing 3 KPIs of the selected object.

To do this, I created a Hierachy Slicer to filter the objects that need to be calculated:

My slicer works fine if I create Measure to calculate each KPI value but it doesn't work when I create a new Data table to store KPI values, I don't know where the problem is, please help me.

[v-lili6-msft]

hi  @Anonymous 

you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary in the visual.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

I know that with measure I can filter data by slicer, but the problem is how to display 3 values ​​'Number of Pass', 'Number of Fail' and 'Total' for each KPI as I wish.

With Measure list, I can only display them as adjacent columns:

[v-lili6-msft]

hi  @Anonymous 

You need to keep a Kpi type into axis in the visual, here is a similar post for you refer to:

https://community.powerbi.com/t5/Desktop/Dynamic-average-on-X-axis-of-a-bar-chart/td-p/1083576

If you still have the problem, please share your sample pbix file and your expected output.

Regards,

Lin

[Anonymous]

Dear @v-lili6-msft,

Can you please explain with an example.

This is the output I want to display:

sample pbix file 

[v-lili6-msft]

hi  @Anonymous 

Just try this way as below:

Step1:

Add a dim kpi type table, then use kpi type field in the bar visual

Step2:

Create another two measure as below:

numberpasskpi = SWITCH( SELECTEDVALUE('Table'[KPI Type]),
"KPI 1",[numberpasskpi1],
"KPI 2",[numberpasskpi2],
"KPI 3",[numberpasskpi3])

totalkpi = SWITCH( SELECTEDVALUE('Table'[KPI Type]),
"KPI 1",[totalkpi1],
"KPI 2",[totalkpi2],
"KPI 3",[totalkpi3])

Step3:

Then drag these two measure into visual

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Hi @v-lili6-msft ,

I need to calculate the KPI Pass ratio, KPI Pass is a KPI with a value greater than or equal to the threshold:

To do this, I have added 1 "Threshold" column to compare with KPI value. How can I count the number of targets with a percentage greater than "Threshold". I find CALCULATE (COUNTROWS ()) unusable in this case

[Anonymous]

Dear @v-lili6-msft ,

Thank you very much for your enthusiastic help, my problem has been resolved.

[Greg_Deckler]

I may not be reading this correctly but it sounds like you are trying to create a dynamic table based upon user interaction. This simply is not possible. Data tables are calculated only at the time of data load (refresh).

[Anonymous]

Hi @Greg_Deckler, I just want to calculate based on the data loaded, every time Hierachy Slicer is selected, the value will change accordingly. I can do this on each Measure, but it doesn't work when I use a table to save the calculated KPI data.