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

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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!