Forum Discussion

New Member

3 years ago

a simple filtered subtotal

Hi thank you in advance for you help. I try to simplify my issues... I'm in EXCEL, Power Pivot and I want to introduce a new calculated measure. myTable Code (key) Area valueA ...

Greg_Deckler

Community Champion

3 years ago

danieleb75 Try:

Column = 
  VAR __ValueA = [valueA]
  VAR __Area = [Area]
  VAR __Total = SUMX(FILTER(ALL('Table'),[Area] = __Area),[valueA])
  VAR __Result = DIVIDE(__ValueA, __Total)
RETURN
  __Result

danieleb75

New Member

3 years ago

Hi Greg,

I'm using measures inside Power Pivot, so I inserted your suggestion just there.

I receive an error at:

VAR __ValueA = [valueA]

is not possibile determinare an unique value. Also adding or not the name of table before.

Thank you.

Greg_Deckler
Community Champion
3 years ago
danieleb75 Try:

Column = VAR __ValueA = MAX([valueA]) VAR __Area = MAX([Area]) VAR __Total = SUMX(FILTER(ALL('Table'),[Area] = __Area),[valueA]) VAR __Result = DIVIDE(__ValueA, __Total) RETURN __Result

I don't work in Excel but rather Power BI Desktop so no idea if this works the same.
- danieleb75
  New Member
  3 years ago
  and than... why the use of "MAX" operator?