Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

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

Need Help

Anonymous

5 years ago

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

Super User

5 years ago

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

Not applicable

5 years ago

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