Forum Discussion
Sumx Total Measure
Hi ,
I have below dataset
| KPI | Actual Value | Program | Region |
| Material | 80 | A | South |
| Material | 70 | B | South |
| Material | 70 | C | South |
| Number of Sites | 1 | A | South |
| Number of Sites | 2 | B | South |
| Number of Sites | 3 | C | South |
Total Sites = Σ Material* Number of Sites =SUM(80*1+70*2+70*3)
I want to create Total Sites Measure, not by calculated columns
Thanks
Anonymous I beleive this is the measure you want:
Total Sites = sumx(values('Table'[Program]), CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Material") * CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Number of Sites"))
7 Replies
Anonymous , pivot this data and try
https://radacad.com/pivot-and-unpivot-with-power-bi
https://youtu.be/oKByyI09Bnothen you can multiple
Or try a measure like
sumx(summarize(Table, Table[Program],Table[Region], "_1", calculate(sum(Table[Actual Value]), Table[KPI] ="Material" ) * calculate(sum(Table[Actual Value]), Table[KPI] ="Number of Sites" )),[_1])
Anonymous , try a measure like
sumx(summarize(Table, Table[Program],Table[Region], "_1", calculate(sum(Table[Actual Value]), Table[KPI] ="Material" ) * calculate(sum(Table[Actual Value]), Table[KPI] ="Number of Sites" )),[_1])
Anonymous - Seems like:
Measure = VAR __Table = ADDCOLUMNS( SUMMARIZE('Table',[Region],[Program],"Value",SUMX(FILTER('Table',[KPI]="Material"),[Actual Value]),"Number",SUMX(FILTER('Table',[KPI]="Number of Sites"),[Actual Value])), "Product",[Value] * [Number] ) RETURN SUMX(__Table,[Product])But, I get the feeling that you shouldn't have unpivoted your KPI column...
Anonymous I beleive this is the measure you want:
Total Sites = sumx(values('Table'[Program]), CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Material") * CALCULATE(sum('Table'[Actual Value]),'Table'[KPI]="Number of Sites"))
