Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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]
)
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading