Resolver I

3 years ago

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.