Summarize table Lookupvalue in Funnel Visual

[gorgo]

Hi  Everyone,

i have the below summarized table "Cover Type Analysis" as per the below :

CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B','Production ReportV20-V1 Power B'[Cover Type],'Production ReportV20-V1 Power B'[Transaction Year],"Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium]))

i have created a new column LastYear:

LastYear = IFERROR(FORMAT(CoverTypeAnalysis[Transaction Year],"General Number") - 1,0)

i need to calculate PercentageYearlyGrowthByCoverType:

PercentageYearlyGrowthByCoverType = 
DIVIDE(CoverTypeAnalysis[Total Premium] - 
LOOKUPVALUE(CoverTypeAnalysis[Total Premium],CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear],
CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]),

ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear],
CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100

i have Transaction Year a Slicer Filter = 2019, everything looks wonderfull and the Funnel is looking correct:

I was asked after that to add Quarter and Month Filters to this report, i added them to the summarized table :

CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B',
'Production ReportV20-V1 Power B'[Cover Type],
'Production ReportV20-V1 Power B'[Transaction Year],
'Production ReportV20-V1 Power B'[Transaction Quarter],
'Production ReportV20-V1 Power B'[Transaction Month],
"Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium]))

and adjusted PercentageYearlyGrowthByCoverType to be as per the below:

PercentageYearlyGrowthByCoverType = 
DIVIDE(CoverTypeAnalysis[Total Premium] - 
LOOKUPVALUE(CoverTypeAnalysis[Total Premium],
CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear],
CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter],
CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month],
CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]),

ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], 
CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear],
CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter],
CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month],
CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100

however im getting wrong results as if it excecute the formula for each row and at the it sums all percentages while what i want is to make the calculation for all selected values:

also i tried to create a mesure but it returned empty and couldnt load it into Funnel Visual

PercentageYearlyGrowthByCoverType mesure = 
DIVIDE(SUM(CoverTypeAnalysis[Total Premium]) - 
LOOKUPVALUE(CoverTypeAnalysis[Total Premium],
CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]),
CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]),
CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]),
CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type])),

ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], 
CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]),
CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]),
CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]),
CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type]))))/100

appreciate your urgent assistance

Thank you in advance

Best regards,

Georges Sabbagh

[technolog · Accepted solution]

It sounds like you're running into an issue with the granularity of your calculations after adding the Quarter and Month filters to your summarized table. The problem you're facing is that the DAX formula is calculating the percentage growth for each row and then summing them up, instead of calculating the percentage growth for the entire selected dataset.

To fix this, you need to adjust the way you're using the LOOKUPVALUE function. When you're using LOOKUPVALUE, it's trying to fetch a single value based on the criteria you've provided. But when you have multiple months or quarters selected, it's not going to fetch the correct value for the entire selection.

Instead of using a calculated column for the PercentageYearlyGrowthByCoverType, you should use a measure. Measures are designed to work with aggregated data, which is what you want.

Here's a suggestion for the measure:

PercentageYearlyGrowthByCoverType Measure =
VAR CurrentYearPremium = SUM(CoverTypeAnalysis[Total Premium])
VAR LastYearPremium =
CALCULATE(
SUM(CoverTypeAnalysis[Total Premium]),
FILTER(
ALL(CoverTypeAnalysis),
CoverTypeAnalysis[Transaction Year] = SELECTEDVALUE(CoverTypeAnalysis[LastYear]) &&
CoverTypeAnalysis[Transaction Quarter] = SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]) &&
CoverTypeAnalysis[Transaction Month] = SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]) &&
CoverTypeAnalysis[Cover Type] = SELECTEDVALUE(CoverTypeAnalysis[Cover Type])
)
)
RETURN
DIVIDE(
CurrentYearPremium - LastYearPremium,
ABS(LastYearPremium)
) / 100
This measure first calculates the total premium for the current year and then calculates the total premium for the last year based on the selected values in your slicers. The FILTER function with the ALL function ensures that the entire table is considered, and then it filters down to the values based on the slicers. Finally, the DIVIDE function calculates the percentage growth.

Try using this measure in your Funnel Visual and see if it gives you the desired results.