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.
camargos88's avatar
camargos88
Icon for Community Champion rankCommunity Champion
6 years ago

Hi AGo ,

 

I didn't get exaclty what you want to do. But perhaps it should work:

 

Measure = CALCULATE(SUM('Table'[Value]);'Table'[Type1]<>"REVENUE")*-1/CALCULATE(SUM('Table'[Value]); FILTER(ALL('Table'); 'Table'[Type1]="REVENUE"))
 
Give it a shoot and let me know, if no give us some example.
 
Ricardo