Forum Discussion

AGo's avatar
AGo
Icon for Post Patron rankPost Patron
6 years ago
Solved

remove filter complicated case issue

Hello, I've got this PBIX  and this measure  Measure = (CALCULATE(SUM('Table'[Value]);'Table'[Type1]<>"REVENUE")*-1)/CALCULATE(SUM('Table'[Value]);'Table'[Type1]="REVENUE") that I'd like to cor...
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi AGo ,

     

    Your requirement is not very clear. However, you can try this measure.

     

    Measure =
    var __nrt1 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALL('Table'),'Table'[Type1] <> "Revenue"))
    var __rt1 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALL('Table'),'Table'[Type1] = "Revenue"))
    var __nrt2 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Type2]),'Table'[Type1] <> "Revenue"))
    var __rt2 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALL('Table'),'Table'[Type1] = "Revenue"))
    var __rt3 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Type3]),'Table'[Type1] = "Revenue"))
    var __nrt3 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Type2],'Table'[Type3]),'Table'[Type1] <> "Revenue"))
    var __t1inscope = ISINSCOPE('Table'[Type1])
    var __t2inscope = ISINSCOPE('Table'[Type2])
    var __t3inscope = ISINSCOPE('Table'[Type3])

    return

    switch(
    true(),
    __t3inscope,DIVIDE(__nrt3,__rt3),
    __t2inscope,DIVIDE(__nrt2,__rt2),
    __t1inscope,DIVIDE(__nrt1,__rt1)
    )
     
     
     
     
     
    Regards,
    Harsh 
     
    If I resolve your problem Mark it as a solution and give kudos.
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi AGo ,

 

Your requirement is not very clear. However, you can try this measure.

 

Measure =
var __nrt1 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALL('Table'),'Table'[Type1] <> "Revenue"))
var __rt1 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALL('Table'),'Table'[Type1] = "Revenue"))
var __nrt2 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Type2]),'Table'[Type1] <> "Revenue"))
var __rt2 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALL('Table'),'Table'[Type1] = "Revenue"))
var __rt3 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Type3]),'Table'[Type1] = "Revenue"))
var __nrt3 = CALCULATE(SUMX('Table','Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Type2],'Table'[Type3]),'Table'[Type1] <> "Revenue"))
var __t1inscope = ISINSCOPE('Table'[Type1])
var __t2inscope = ISINSCOPE('Table'[Type2])
var __t3inscope = ISINSCOPE('Table'[Type3])

return

switch(
true(),
__t3inscope,DIVIDE(__nrt3,__rt3),
__t2inscope,DIVIDE(__nrt2,__rt2),
__t1inscope,DIVIDE(__nrt1,__rt1)
)
 
 
 
 
 
Regards,
Harsh 
 
If I resolve your problem Mark it as a solution and give kudos.
AGo's avatar
AGo
Icon for Post Patron rankPost Patron
6 years ago

Your solution worked like a charm for costs! Thanks. The only strange fact is that the measure gives a result of 76% under REVENUE that should be totalrevenue/totalrevenue=x/x=1=100% or better hidden

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    Hi AGo ,

     

    Thanks that you liked the solution.

     

    Please use this formula for __nrt1. This will remove the 77% from Revenue.

     

    var __nrt1 = CALCULATE(SUMX('Table','Table'[Value]),FILTER('Table','Table'[Type1] <> "Revenue"))
     
    Thanks and Regards,
    Harsh Nathani