cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

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

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

1 ACCEPTED SOLUTION
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Helper II

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

Helper II

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

Community Support

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,

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper II

Hi Greg,

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.