Forum Discussion
DAX - Fetch distinct sum when relationship is undefined
Hi,
I have a two tables
Forecast Table which has multiple forecasts for a given quarter
+------------+------------+------------+-----------+ | Fiscal Qtr | Type | Category | Revenue | +------------+------------+------------+-----------+ | FY2021Q3 | Forecast 2 | Books | 633 | | FY2021Q3 | Forecast 1 | Books | 676 | | FY2021Q2 | Forecast 1 | Books | 619 | +------------+------------+------------+-----------+
Actual Table:
+--------------+-----------+----------------+-----------+ | Fiscal Qtr | Type | Product Family | Revenue | +--------------+-----------+----------------+-----------+ | FY2021Q3 | Actuals | Books | 217 | | FY2021Q2 | Actuals | Books | 559 | +--------------+-----------+----------------+-----------+
I have defined a relationship between the quarters and category column and when I pull both the forecast and actuals into one view, the revenue columns are getting added up (which is expected since there is no relationship defined for the TYPE column and its resulting in row duplication
How to I write DAX to get the desired result below "Revenue from Actual Table"?
+------------+------------+------------+-----------+---------------------------+
| Fiscal Qtr | Period | Category | Revenue | Revenue from Actual Table |
+------------+------------+------------+-----------+---------------------------+
| FY2021Q3 | Forecast 2 | Books | 633 | 217 |
| FY2021Q3 | Forecast 1 | Books | 676 | 217 |
| FY2021Q2 | Forecast 1 | Books | 619 | 559 |
+------------+------------+------------+-----------+---------------------------+3 Replies
- amitchandak
Super User
Anonymous , You have to create a common Fiscal Qtr, or Fiscal Calendar table and join with both of them and then use Fiscal Qtr from that table
Fiscal Qtr = distinct(union(distinct(forecast[Fiscal Qtr]),distinct(actual[Fiscal Qtr]))
Refer common table : https://www.youtube.com/watch?v=Bkf35Roman8
refer: https://radacad.com/many-to-one-or-many-to-many-the-cardinality-of-power-bi-relationship-demystified
Your solution helped fix the actuals revenue but the aggregation on the forecast column is all messed up. Why do you think that is?
+--------------+------------+--------------+--------------+----------------+------------------------+
| Fiscal Qtr | Category | Period | Actual Rev | Forecast Rev | Correct Forecast Rev |
+--------------+------------+--------------+--------------+----------------+------------------------+
| FY2021Q3 | Books | Forecast 1 | 217 | 676 | 676 |
| FY2021Q3 | Books | Forecast 2 | 217 | 1,252 | 633 |
| FY2021Q2 | Books | Forecast 1 | 559 | 676 | 619 |
| FY2021Q2 | Books | Forecast 2 | 559 | 1,252 | - |
+--------------+------------+--------------+--------------+----------------+------------------------+
