不同粒度的两列的 DAX 条件和 [英] DAX conditional sum of two columns with different granularity

问题描述

这是

解决方案

我认为这会奏效:

预期结果 =VAR总结=总结(Unique_Manager,Unique_Manager[经理],Budget_Brand"，总和(Budget_Brand[BudgetBrand])，Budget_Product"，SUM(Budget_Product[BudgetProduct]))返回总和(概括，IF(ISBLANK([Budget_Product])、[Budget_Brand]、[Budget_Product]))

这按 Manager 分组并计算一个汇总表，其中包含 BudgetBrand 和 BudgetProduct 的总和，并使用SUMX 使用指定的逻辑.

<小时>

这里有一个更简洁的实现

预期结果 =总和(值( Unique_Manager[Manager] )，VAR SumBrand = CALCULATE (SUM (Budget_Brand[BudgetBrand]))VAR SumProduct = CALCULATE (SUM (Budget_Product[BudgetProduct]))返回IF ( ISBLANK ( SumProduct ), SumBrand, SumProduct ))

我是这个，我们不需要计算表来迭代.相反，我们在本地过滤器上下文中迭代 Manager 的所有不同值，并在该上下文中求和 BudgetBrand 和 BudgetProduct.请注意，我已将总和包含在 CALCULATE 中.这样做是为了执行从 SUMX 内的行上下文(特定的 Manager)到将该 Manager 作为过滤器上下文的上下文转换code>BudgetBrand 和 BudgetProduct.将这些总和存储为变量会使 IF 行更具可读性，并且只需要计算一次 SumProduct 而不是两次.

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.

解决方案

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 条件和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！