DAX formula to combine two criteria and output as one

[royr001]

Hello

I'd like to incorporate a dax formula or other dynamic function into my power pivot to have an additional criteria added which is the sum of two other criteria.

Here is a snapshot of a basic power pivot example of my workbook. I have 4 categories and I want a 5th category in the pivot table which is the sum of Revenue and Support (boxed in red).

 

[Matt Allington]

Tricky. I think you could create another table with the 5 groups and account numbers. It would be identical to the reference table you have, but contain 2 extra rows, the 100 and 400 rows are repeated with “group” = Rev + Support.

Join in the new table to the existing reference table.

Write a measure = CALCULATE(sum(data[amount]),NewTableName)

place the group column from the new table in the pivot columns (replace the current group from ref table).
place the new measure in values.

It will work, guaranteed. ��

 

[VBA Geek]

Hi royr

in addition to what Matt suggested, I wanted to add that what you are trying to do can be solved with a many to many pattern by constructing the below model:

And then using this measure:

=CALCULATE(
    SUM( Sales[Amount] ),
    CROSSFILTER( AccountsGroups[Acc_Num], Accounts[Acc_Num], Both )
)