Forum Discussion
Power BI - Dynamic measures inside disconnected table
I have a KPI report with 13 different datasets. For each KPI there is a measure to calculate the actual KPI score which later will be transformed to a score on a scale from 1-5.
There is also a Region/Country/Site core data with relationship to all datasets. So from slicers all of the measures are able to slice on region, country & site.
For my last part of the report I now need to put all of the measure to their corresponding name in a table and from there I need to make a new column with calculations based on the scale.
Example of free from table/disconnected table below
| Number | KPI Name | Actual | Scale | 1 | 2 | 3 | 4 | 5 | Actual Score (1-5) after Scaling |
| 1 | Stakeholder Satisfaction Index | [Measure1] (example 3) | +/- 1 | 2.5 | 3 | 3.5 | 4 | 4.5 | (calculated column) (example score from measure on the scale equals to 2) |
| 2 | User Experiance Survey | [Measure2] | +/- 1 | 2.8 | 3.3 | 3.8 | 4.3 | 4.8 | (calculated column) |
| 3 | Service Point Survey | [Measure3] | +/- 1 | 2.8 | 3.3 | 3.8 | 4.3 | 4.8 | (calculated column) |
| 4 | Service Request Feedback | [Measure4] | +/- 1 | 2.8 | 3.3 | 3.8 | 4.3 | 4.8 | (calculated column) |
| 5 | Food Waste | [Measure5] | +/- 0.05 | 0.018 | 0.043 | 0.068 | 0.093 | 0.118 | (calculated column) |
I have tried this code below as a start to build this but I got stuck now when the measure doesn't filter based on the slicer.
How can I build this table? It needs to be able to calculate the last column (Actual Score after Scaling) based on the measure. But also it needs to respond to the slicers.
UNION(
ROW("KPI", "Food Waste", "Actual", CALCULATE('Food Waste'[Actual Food Waste],'Site Core Data'[Region] = SELECTEDVALUE('Site Core Data'[Region]))))