topic Re: Sum with filtered measures in DAX Commands and Tips

Sum with filtered measures

letherboi — Wed, 25 Mar 2020 16:17:03 GMT

Hello! I have a situation where I am using Calc(sum()) to sum filtered data and then subtract 40 from the result. The table has correct individual results, but is not rolling the sub total correctly.

Equation:

var OT=(CALCULATE(sum(DataTbl[HRS])-40,DateTbl[DaysBack]<=7,DateTbl[DaysBack]>0))

RETURN

if(OT>0,OT)

The above sums all hrs within 7 prior days and then subtracts 40 from the sum. I have taken a look at SUMX() but am not sure how I can incorporate the subtraction of 40 post sum and then find that total

Example of Issue where Sub total should be 90:

Sub Total	(170-40)=130
Name 1	(60-40)=20
Name 2	(110-40)=70

Re: Sum with filtered measures

Greg_Deckler — Wed, 25 Mar 2020 16:36:52 GMT

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

Re: Sum with filtered measures

letherboi — Wed, 25 Mar 2020 20:50:17 GMT

The BI you provided gave ample direction for my solution! It took some tweaking, but was able to configure once the understanding was established.

Re: Sum with filtered measures

letherboi — Wed, 22 Apr 2020 00:38:50 GMT

When placed in a large data set, the summarize method becomes a hefty task for BI and requires a long time for calculations to finish.

I have attempted to move the Summarize portion out into a calculated field and maintain the Sumx() part with no luck. The calculations returns accurately, but the subtotals sum the whole Table (Counttble) and do not filter. Is it possible to do this split of equation to help performance?

Table:

Counttbl = SUMMARIZE(DataTbl,DataTbl[EDATE],'Map'[ID],"__value",[CountSet2])

Greg_Deckler

Measure:

CountSet = IF(HASONEVALUE('Map'[ID]),[CountSet2],SUMX('Counttbl',[__value]))

Calculation:

CountSet2 = CALCULATE(DISTINCTCOUNT(DataTbl[rID]),FILTER(Calendar,Calendar[CALNDR_DT]))