a simple filtered subtotal

[danieleb75]

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 (explicit calculated field)	% Value of single record respect of Sub-Totale Area ValueB (what I need)
Cod001	Area1	100€	40%
Cod002	Area1	150€	60%
Cod003	Area2	200€	100%
Cod004	Area3	300€	50%
Cod005	Area3	200€	33%
Cod006	Area3	100€	17%

I try to use EARLIER and EALRIEST but...

=[valueA] / CALCULATE(SUMX(FILTER(myTable;[area]=EARLIER(TAB_Baseline[area]));[valueA]))

doesn't work cause of area is not used before (dax error response message).

I think also that this way is cpu/time wasting cause of every row is has to re-calculate all sub-total.

What is the best way to have what I need?

Thank you

Dan

[Greg_Deckler]

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

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]

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

and than... why the use of "MAX" operator?