Search code examples
powerbidax

DAX Total in Matrix

I have the following DAX expression:

Daily Turnover = 
VAR applNos = 
CALCULATETABLE(
    DISTINCT(combined_apps[appl_appl_no]), 
    combined_apps[table_type] = "TRAN",
    combined_apps[appl_decision_date] IN VALUES ('calendar'[cal_posted_date])
    )

RETURN

CALCULATE(
  SUM(combined_apps[tran_amount]),
  REMOVEFILTERS('calendar'),
  combined_apps[appl_appl_no] IN applNos,
  combined_apps[table_type] = "TRAN"
 )

When placed into a matrix, with my date field, 'calendar'[cal_posted_date] in the rows, the individual rows calculate correctly, however my total is "incorrect". I understand totals in measures don't work as simply as summing up the column, however all variations of this code I get the same issue. Image below...

enter image description here

Esentially, what I want my measure to do is sum up all combined_apps[tran_amount] for each date in 'calendar[cal_posted_date], however it needs to first take all the appl_appl_no that is found on that date, and then calculate the sum for those numbers in the rest of the data.

Solution

  • I suggest creating a new measure by adjusting the total to be the sum of the 'calendar[cal_posted_date]' column:

    Daily Turnover Corrected Total = 
SUMX(
    VALUES('calendar'[cal_posted_date]),
    [Daily Turnover]
)