Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 N...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • DataZoe's avatar
    DataZoe
    6 years ago

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

     

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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