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, c...
Need Help
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