DAX conditional sum of two columns with different granularity

Question

This is follow up question of the one asked here. However this time two columns have different granularity and are located in different tables. So simple SUMX solution proposed earlier is not applicable. I attach SumDifferntGranularity.pbix file.

How to construct a DAX measure which returns sum of either BudgetProduct (if possible) or BudgetBrand. The logic is take Brand if Product is empty. So expected results looks like this:

+---------+-------------+---------------+-----------------+
| Manager | BudgetBrand | BudgetProduct | Expected result |
+---------+-------------+---------------+-----------------+
| Alice   |          16 |            15 |              15 |
| John    |           7 |               |               7 |
| Martha  |          21 |            21 |              21 |
| Zadar   |          11 |               |              11 |
+---------+-------------+---------------+-----------------+
| Total   |          55 |            36 |              54 |
+---------+-------------+---------------+-----------------+

In this example, all Managers have budget defined on Brand, but some Managers (Alice and Martha) have budget defined on Products. How to construct a measure which will take budget defined on products, if possible, but if not possible then it will take the budget defined on Brands.

Alexis Olson Alexis Olson · Accepted Answer · 2019-08-29T16:00:28

I think this will work:

Expected Result =
VAR Summary =
    SUMMARIZE (
        Unique_Manager,
        Unique_Manager[Manager],
        "Budget_Brand", SUM ( Budget_Brand[BudgetBrand] ),
        "Budget_Product", SUM ( Budget_Product[BudgetProduct] )
    )
RETURN
    SUMX (
        Summary,
        IF ( ISBLANK ( [Budget_Product] ), [Budget_Brand], [Budget_Product] )
    )

This groups by Manager and calculates a summary table with the sum for BudgetBrand and BudgetProduct for each and the iterates through this summary table with SUMX using the logic specified.

Here's a bit cleaner implementation

Expected Result =
SUMX (
    VALUES ( Unique_Manager[Manager] ),
    VAR SumBrand = CALCULATE ( SUM ( Budget_Brand[BudgetBrand] ) )
    VAR SumProduct = CALCULATE ( SUM ( Budget_Product[BudgetProduct] ) )
    RETURN
        IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct )
)

I this one, we don't need a calculated table to iterate over. Instead, we iterated over all the distinct values of Manager in the local filter context and sum BudgetBrand and BudgetProduct within that context. Note that I've wrapped the sums in CALCULATE. This is done to perform the context transition from the row context inside SUMX (the particular Manager) to having that Manager as a filter context on BudgetBrand and BudgetProduct. Storing these sums as variables makes for a more readable IF line and only requres SumProduct to be computed once instead of twice.

DAX conditional sum of two columns with different granularity

1 Answers