Search code examples
powerbidax

Calculate Sub Total as Row

Suppose I have a data model joined by category.id and transaction.category_id on these two tables:

category:

id code
1 a
2 b
3 c

transaction:

category_id amount
1 100
1 200
1 300
2 50
2 100
2 200

In a table visualization in PowerBI, if I just bring category and amount as is, I get the following as expected. a and b have transactions and the sum of each is displayed accordingly:

enter image description here

But is there a way to show category c? maybe as a total of category a and b, and still have the Total line total correctly?

For example, here a and b sum by category but c sums a collective total of a and b:

enter image description here

Is this possible with DAX as a measure field?

Solution

  • This would be the most straightforward approach that returns the expected result.

    Amount with Subtotals = 
SUMX (
    Categories,
    IF (
        Categories[Category Code] = "c",
        CALCULATE (
            SUM ( Transactions[Amount] ),
            REMOVEFILTERS ( Categories ),
            TREATAS ( { "a", "b" }, Categories[Category Code] )
        ),
        CALCULATE ( SUM ( Transactions[Amount] ) )
    )
)

    result