Last value instead of average in total

[Anonymous]

I have a matrix in powerbi with 3 measures that are calculated as a weighted average. The total column is showing the average of the columns displayed but I want it to display the last value instead. Any idea how can I do that?

The formula I am using to calculate the KPI is:

SUMX(

table1,

table1[Average]*table1[weight])/
SUM(table1[weight])

Something like this

	q1	q2	q3	q4	Total now	Total desired
KPI 1	5	8	9	10	8	10
KPI 2	6	1	9	3	4.75	3
KPI 3	6	4	6	2	4.5	2

Thank you!

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

Let’s assume that your metric is called value

Value = SUMX(
table1,
table1[Average]*table1[weight])/
SUM(table1[weight])

You create the following measure using ISINSCOPE.

Final Value = IF(ISINSCOPE('table1'[q]),[Value],CALCULATE([Value],FILTER('table1',[q]="q4")))

Attached is the sample I created, there may be some discrepancies, you can refer to it.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-stephen-msft 

Thank you so much, it worked exactly as I wanted it.

[thedatahiker]

@Anonymous You can try something like this.

In this solution, you will need a date table. 

Var TableWeight =  SUM ( Table1[Weight] )
RETURN
CALCULATE (
DIVIDE ( Table1[Average] * Table1[Weight], TableWeight ),
LASTDATE( 'Date'[Date] )
)