How to display Totals in Total row for Calculated (using Measure) field

[nhuda]

Hello everyone,

I have a date slider filter and calculated cumulative fields using measures. Period totals show up fine as usual, but for the measures, there's no Summarization option and as such, can't display the total.

I tried creating a column using the same formula as a measure so I have the Summarization option but it doesn't display any data even for the data rows.

Any idea would be highly appreciated.

Thank you,

nhuda

[nhuda]

Hello @Greg_Deckler ,

I am encountering 1 issue with this formula. So when there's data for the period selected in date slider, the cumulative calculation works fine, but when data for the period is none (no data), then the cumulative is coming up as blank as well, even though the cumulative should have data.

Any idea how this can be resolved/

Thank you,

nhuda

[nhuda]

Never mind, I was abale to make it work combining your formula here: https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/5...

and my formula for cumulative calculation, it got little complicated, but did work. Here's one of them:

Cumulative Tested = 
VAR _select=SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[Project])
VAR _pStartDate = MINX(FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),'vw_PRBI_Report_WeldRepairStats'[Project]=_select),[ProjectStartDate])
VAR _fromDate=MAXX(ALLSELECTED('vw_PRBI_Report_WeldRepairStats'),'vw_PRBI_Report_WeldRepairStats'[TestedDate])
VAR __table = 
SUMMARIZE('vw_PRBI_Report_WeldRepairStats',[MaterialGroup],"__value",
    SUMX(
        FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_fromDate &&
        'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
        'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
        ,IF(ISBLANK([Tested]) || [Tested] = 0, 0, [Tested])
    )
)
RETURN
IF(
    HASONEVALUE(vw_PRBI_Report_WeldRepairStats[MaterialGroup]),
    SUMX(
        FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
        'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_fromDate &&
        'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
        'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
        ,IF(ISBLANK([Tested]) || [Tested] = 0, 0, [Tested])
    ),
    SUMX(__table,[__value])
)

Thanks again Greg!

nhuda

[v-kongfanf-msft]

Hi  @nhuda ,

Did @Greg_Deckler  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[Greg_Deckler · Accepted solution]

@nhuda First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[nhuda]

Hi Greg, 

Thanks for your response and informative links provided.

I voted for your idea.

For my issue, I noticed this behavior: based on date range selection when there's one row of data, it's showing up in the Total row, but twhen there're other rows even with 0s, the totals disappear. Please see below:

And here's the formula for the measure that calculates cumulative for the rows for each group: 

Cumulative Tested = 
var _select=SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[Project])
VAR _pStartDate = MINX(FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),'vw_PRBI_Report_WeldRepairStats'[Project]=_select),[ProjectStartDate])
var _fromDate=MAXX(ALLSELECTED('vw_PRBI_Report_WeldRepairStats'),'vw_PRBI_Report_WeldRepairStats'[TestedDate])
return
SUMX(
    FILTER(ALL('vw_PRBI_Report_WeldRepairStats'),
    'vw_PRBI_Report_WeldRepairStats'[TestedDate]>=_pStartDate && 
    'vw_PRBI_Report_WeldRepairStats'[TestedDate]<=_fromDate &&
    'vw_PRBI_Report_WeldRepairStats'[Project]=_select && 
     'vw_PRBI_Report_WeldRepairStats'[MaterialGroup] = SELECTEDVALUE('vw_PRBI_Report_WeldRepairStats'[MaterialGroup]))
    ,[Tested]
)

Any idea how I can make this work.

Thank you,

nhuda