CODEWITHSUNDEEP
averagedashboardspotfiretibco
Porridge
Porridge

Reputation: 109

Average based on distinct values in another column

For my project I need to create a bar-chart where category axis is the average of an aggregated sum and value axis is rowcount(). I can't find a way to calculate the average of an aggregated sum directly in the bar-chart.

Here you can find the dxp file with steps required to calculate the final value (85,26).

I came to a solution using the calculate columns (highlighted in the dataset attached), but I need to calculate it directly in the bar-chart so it will change when filters are applied .

I tried to explain better. My KPI is calculated using the following formula:

Sum([Weight]) OVER ([Period],[ID Player],[Player Type],[ID Game],[Player Role])) OVER ([ID 
Player],[Player Type],[ID Game],[Player Role])>

The average is done on the following values:

(83,50 83,50 83,50 83,50 83,50 83,50 83,50 83,50 86,14 86,14 86,14 86,14 86,14 86,14 86,14 86,14)/16 = 84,82

So I tried to add DISTINCT but the result was still incorrect since the average is done for the following values:

(83,50+86,14)\2 = 84,82

The average should be based on distinct values from "Period" column so

(83,50+86,14+86,14)\3 = 85,26

Can anyone help me to find a formula to obtain the last average?

Thanks in advance!

EDIT1: I tried a formula that's close to the suggestion in the comments:

Sum([Weight] * [Score]) OVER ([Period],[ID Player],[Player Type],[ID Game],[Player Role]) / Sum([Weight]) OVER ([Period],[ID Player],[Player Type],[ID Game],[Player Role])
,null)) OVER ([ID Player],[Player Type],[ID Game],[Player Role])

But it will do the average taking just first values for each period (60+97+97/3=84,67) enter image description here

Upvotes: 0

Views: 480

Answers (1)

Monte_fisto
Monte_fisto

Reputation: 745

Not sure how you have set up the visualisation, but something like the following formula might work

Avg(case when Rank(RowId(),[Period])=1 then [final Score] end)

Upvotes: 0

Related Questions