Forum Discussion
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 (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
4 Replies
- Greg_Deckler
Community Champion
danieleb75 Try:
Column = VAR __ValueA = [valueA] VAR __Area = [Area] VAR __Total = SUMX(FILTER(ALL('Table'),[Area] = __Area),[valueA]) VAR __Result = DIVIDE(__ValueA, __Total) RETURN __ResultHi 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 __ResultI don't work in Excel but rather Power BI Desktop so no idea if this works the same.
