Forum Discussion

nhuda's avatar
nhuda
Icon for Helper II rankHelper II
2 years ago
Solved

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

Need Help

5 Replies

  • nhuda's avatar
    nhuda
    Icon for Helper II rankHelper II
    2 years ago

    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/547907

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      nhuda
      Icon for Helper II rankHelper II
      2 years ago

      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

  • nhuda's avatar
    nhuda
    Icon for Helper II rankHelper II
    2 years ago

    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