Sumx Total Measure

[Anonymous]

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

[DataZoe · Accepted solution]

@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"))

 

[Anonymous]

Correct. I m looking for this one

[Greg_Deckler]

@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...

[amitchandak]

@Anonymous , pivot this data and try

https://radacad.com/pivot-and-unpivot-with-power-bi
https://youtu.be/oKByyI09Bno

then 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]

My requirement is to do without Pivot data.Is there any method do without pivoting?

[amitchandak]

@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]

getting this error