Display calculated values in a single table
Hello Everyone, I have an output table such that: Problem : I want to display this data in my power bi report. My Table : Data points as labels need to be added separately as they are not inside the database I want to show DATAPOINT 6 as per the slicer value selected. but the problem is By default it only calculates for fixed values i.e., Age = TOTAL & Unit = THS_PER Formula for Datapoint 6 : ((Datapoint 1 / Datapoint 2) - 1 ) *100 When I apply Slicer of Age and Unit such that ; Age = Y_LT18 and Unit = PC then Datapoint 6 does not calculate data for it. This is the default value coming, but I want to show Datapoint 6 ( Change YoY) when the Slicer value gets changed. As you can see above, if I change Slicer values i.e., When Unit = PC , and Age = Y_LT18. YoY gets removed and does not calculate for PC units and Age also. I hope my problem is understood. My Current Formula : _POC = VAR Max_Year = MAX('2DataPoints'[CurrentYear]) VAR Max_Obs_Value = CALCULATE(MAX('2DataPoints'[OBS_Value]), '2DataPoints'[CurrentYear] = Max_Year) VAR Prior_Year = Max_Year - 1 VAR Prior_Obs_Value = CALCULATE(MAX('2DataPoints'[OBS_Value]), '2DataPoints'[CurrentYear] = Prior_Year) VAR YOY_Percent = IF(Prior_Obs_Value = 0, BLANK(), ((Max_Obs_Value / Prior_Obs_Value)-1) * 100) VAR Max_AGe = SELECTEDVALUE('2DataPoints'[Age],"PERCENTAGE") VAR Max_Unit = SELECTEDVALUE('2DataPoints'[Unit]) Var Max_Geo = CALCULATE(MAX('2DataPoints'[Geo]),'2DataPoints'[CurrentYear]=Max_Year) Var shortTerm = Max_Year - 5 Var ShortTermValue = CALCULATE(MAX('2DataPoints'[OBS_Value]), '2DataPoints'[CurrentYear] = shortTerm) Var ChangeinShortTerm = IF(ShortTermValue = 0 ,BLANK(),((Max_Obs_Value/ShortTermValue)-1)*100) RETURN UNION( SELECTCOLUMNS( FILTER('2DataPoints', '2DataPoints'[CurrentYear] = Max_Year), "Year", [CurrentYear], "Value", [OBS_Value], "Geo",'2DataPoints'[Geo], "Age",'2DataPoints'[Age], "Unit",'2DataPoints'[Unit], "Type", "Current Year" ), SELECTCOLUMNS( FILTER('2DataPoints', '2DataPoints'[CurrentYear] = Prior_Year), "Year", [CurrentYear], "Value", [OBS_Value], "Geo",'2DataPoints'[Geo], "Age",'2DataPoints'[Age], "Unit",'2DataPoints'[Unit], "Type", "Prior Year" ), SELECTCOLUMNS( FILTER('2DataPoints', '2DataPoints'[CurrentYear] = Max_Year - 5), "Year", [CurrentYear], "Value", [OBS_Value], "Geo",'2DataPoints'[Geo], "Age",'2DataPoints'[Age], "Unit",'2DataPoints'[Unit], "Type", "Short term" ), ROW("Year",Prior_Year &"-" & Max_Year, "Value", YOY_Percent,"Geo",Max_Geo,"Unit",Max_Unit,"Age", Max_AGe,"Type",ChangeYoy), CALCULATETABLE(ROW("Year",shortTerm &"-" & Max_Year, "Value", ChangeinShortTerm,"Geo",Max_Geo,"Unit","Total","Age","THS_PER" ,"Type", "Change Short Term "),ALL('2DataPoints')) ) I just want it to calculate a few values of this according to slicers but it does not calculate. Also, I want to add the Year filter also. If I select Year from Slicer then based on that Year all values should be calculated. P.S. : I have created this as a new table since I want to add Datapoints also.
