Forum Discussion

letherboi's avatar
letherboi
Frequent Visitor
6 years ago
Solved

Sum with filtered measures

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

3 Replies

    • letherboi's avatar
      letherboi
      Frequent Visitor
      6 years ago

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

    • letherboi's avatar
      letherboi
      Frequent Visitor
      6 years ago

      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])
       
       
      Measure:
      CountSet = IF(HASONEVALUE('Map'[ID]),[CountSet2],SUMX('Counttbl',[__value]))
       
      Calculation:
      CountSet2 = CALCULATE(DISTINCTCOUNT(DataTbl[rID]),FILTER(Calendar,Calendar[CALNDR_DT]))