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

问题描述

这是在

解决方案

我认为这会起作用：

 预期结果= 
 VAR摘要= 
 SUMMARIZE（
 Unique_Manager，
 Unique_Manager [Manager]，
 Budget_Brand，SUM（Budget_Brand [BudgetBrand]），
 Budget_Product，SUM（Budget_Product [BudgetProduct] ）
）
返回
 SUMX（
摘要，
 IF（ISBLANK（[Budget_Product]），[Budget_Brand]，[Budget_Product] ）
）
  

由 Manager 并计算出一个汇总表，每个表的总和分别为 BudgetBrand 和 BudgetProduct ，并通过此汇总表进行迭代 SUMX 使用指定的逻辑。

---

这是一个更干净的实现

 预期结果= 
 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）
）
  

在这个例子中，我们不需要计算表即可进行迭代。相反，我们在本地过滤器上下文中遍历了 Manager 的所有不同值，并对 BudgetBrand 和<$ c $求和c> BudgetProduct 在该上下文中。请注意，我将这些总和包装在 CALCULATE 中。这样做是为了执行从 SUMX （特定的 Manager ）内部的行上下文到具有 Manager 作为 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屋！