cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OPS-MLTSD
Helper V
Helper V

subtract the summation of two different categories from the total

Hello, I am trying to add up the data from two different categories first and then subtract the value from the total, I created this measure but it is giving me an error, if someone could please help me fix this error, that would be great. thank you:

 

Measure =

VAR pl = CALCULATE(

CALCULATE(

    COUNT('Table'[Client ID]),

    FILTER(‘Table’, ‘Table’[Status] = "Active"),

    FILTER(‘Table’, ‘Table’[Category] = "B" )

   ) +

CALCULATE(

    COUNT(‘Table’[Client ID]),

    FILTER(‘Table’, ‘Table’[Status] = "Active"),

    FILTER(‘Table’, ‘Table’[Category] = "C" )

   )

)

 

VAR tot = CALCULATE(

    COUNT(‘Table’[Client ID]),

    FILTER(‘Table’, ‘Table’[Status] = "Active"),

    FILTER(‘Table’, ‘Table’[Category] <> NULL)

   )

 

Return

CALCULATE(SUMX(tot))-CALCULATE(SUMX(pl))

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

"<> NULL" won't work. You need to use ISBLANK instead.

 

Also, you don't need to write CALCULATE ( SUMX (...) ) in the final line.

 

Try this:

Measure1 =
VAR pl =
    CALCULATE (
        COUNT ( 'Table'[Client ID] ),
        FILTER ( 'Table', 'Table'[Status] = "Active" ),
        FILTER ( 'Table', 'Table'[Category] IN { "B", "C" } )
    )
VAR tot =
    CALCULATE (
        COUNT ( 'Table'[Client ID] ),
        FILTER ( 'Table', 'Table'[Status] = "Active" ),
        FILTER ( 'Table', NOT ISBLANK ( 'Table'[Category] ) )
    )
RETURN
    tot - pl

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

"<> NULL" won't work. You need to use ISBLANK instead.

 

Also, you don't need to write CALCULATE ( SUMX (...) ) in the final line.

 

Try this:

Measure1 =
VAR pl =
    CALCULATE (
        COUNT ( 'Table'[Client ID] ),
        FILTER ( 'Table', 'Table'[Status] = "Active" ),
        FILTER ( 'Table', 'Table'[Category] IN { "B", "C" } )
    )
VAR tot =
    CALCULATE (
        COUNT ( 'Table'[Client ID] ),
        FILTER ( 'Table', 'Table'[Status] = "Active" ),
        FILTER ( 'Table', NOT ISBLANK ( 'Table'[Category] ) )
    )
RETURN
    tot - pl

View solution in original post

Thank you so much!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.