DAX - Fetch distinct sum when relationship is undefined

[Anonymous]

Hi,

I have a two tables

1. 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  |
   +------------+------------+------------+-----------+

2. 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  |
    +------------+------------+------------+-----------+---------------------------+

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Check the measure.

Measure = CALCULATE(SUM(Actual[Revenue]),FILTER(Actual,Actual[Fiscal Qtr]=SELECTEDVALUE(Forecast[Fiscal Qtr])&&Actual[Category]=SELECTEDVALUE(Forecast[Category])))

Best Regards,

Jay

[amitchandak]

@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

[Anonymous]

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 | - |
+--------------+------------+--------------+--------------+----------------+------------------------+