SUMMARIZE not showing numbers for SUMX measure

[fred160358]

I have a measure called "Reudction in Cost/Test or Treatment"  (Total Cost/Volume)

Total Cost

CALCULATE(SUM('Imaging Financials'[Actuals Current Month]), 'Imaging Financials'[Rev/Exp] = "EXPENSES" , 'Imaging Financials'[Hosp/Amb] = "Laboratory" ) + CALCULATE(SUM('Imaging Financials'[Actuals Current Month]), 'Imaging Financials'[Rev/Exp] = "ALLOCATION", 'Imaging Financials'[Allocation_Type] <> "REVENUES", 'Imaging Financials'[Hosp/Amb] = "Laboratory" )

Volume

CALCULATE(SUM('Imaging Financials'[Actuals Current Month]), 'Imaging Financials'[Account ID] = "902230", 'Imaging Financials'[Rev/Exp] = "Vol")

I created a summarize table so I can have month, quarter, and year stored in the same column, so I can display the data in matrix like the following:

Measure      Sep-18      Oct-18     Nov-18         Q1                     YTD
Reduction     23.74        31.54        28.74      Sum of three months    Sum of months

SUMX to get quarter sum

SUMX(VALUES('Date'[Month]), [M-(Lab Dash) Reduction in Cost/Test or Treatment])

SUMMARIZE TABLE (summarize by 'Date'Month, 'Date'Quarter, 'DateFiscalYr')

UNION( addcolumns(SUMMARIZE('Imaging Financials', 'Date'[Fiscal_Yr], 'Date'[Month], "Value", [M-(Lab Dash) Reduction in Cost/Test or Treatment]), "Measure", "Reduction in Cost/Test or Treatment (LAB)" ), //Month

addcolumns(SUMMARIZE('Imaging Financials', 'Date'[Fiscal_Yr], 'Date'[Fiscal_YrQtr], "Value", 'Imaging Financials'[M-(Lab Dash) Reduction in Cost/Test or Treatment (Quarter Sum)]), "Measure", "Reduction in Cost/Test or Treatment (LAB)" ), //Quarter

addcolumns(SUMMARIZE('Imaging Financials', 'Date'[Fiscal_Yr], 'Date'[C-FsicalYTD], "Value", [M-(Lab Dash) Reduction in Cost/Test or Treatment (Quarter Sum)]), "Measure", "Reduction in Cost/Test or Treatment (LAB)" ) //YTD
)

Issue:

There is no value for Quarterly and Yearly data. (See below)

Fiscal_Yr         Month        Value     
2019             Mar-19         22.31
2019             Jan-19         23.41
2019             Q1
2019             YTD-19

It has show the correct data if I drag the 'Date'Quarter and SUMX measure into a table.

Discal_YrQtr                 M-Reudction in Cost (Quarter Sum)
F2019-Q1                             77.85
F2019-Q2                             66.45

[v-shex-msft · Accepted solution]

Hi @fred160358 ,

I think you need to use variable to store summarize table, quarter/year total both need to summarize based on that variable. It is impossible to use original measure formula to calculate with quarter, ytd.(they not really existed in tables).

If you are confused on coding formula, please share some sample data for test.

Regards,

Xiaoxin Sheng