cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ESDC
Resolver I
Resolver I

Summarize Dax (without summarizing text strings that are blank)

Hello I have the following Dax which aims at Summarizing all aircraft who that have a value of $100,000 or more per Class which also have a Correct Company Name, but my output is also including $110,000 which is being calculated (Bellanca $20,000 plus Lancair $90,000). Although it is true that they are part of the Single Piston Class and are over $100,000, they are not in my Correct Company Name list and therfore I do not want them to be calculated. What can I add to this Dax so it avoids calculating a blank value in Correct Company Name? 

 

100k+ (SUMMARIZE) = 
SUMX(
    FILTER(
        SUMMARIZE(
        Database,
        'Class Lookup'[Class],
        CompanyNameLookup[Correct Company Name],
         "_1",
         SUM(Database[Amount])
         ),
         [_1]>=100000
         )
         ,[_1]
         )

 

 

Capture.JPG
THe correct answer I am looking for should not include the calculation of the SIngle Piston Column which has a blank value under Correct Company name column. 

 

My database and Lookup tables look like this. 

Database.JPG

 

Thank you!

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Please try this expression instead.  You could also use the Visual Level filter for your table and uncheck the Blank values from the Correct Company Name column.

 

100k+ (SUMMARIZE) =
SUMX (
    FILTER (
        FILTER (
            SUMMARIZE (
                Database,
                'Class Lookup'[Class],
                CompanyNameLookup[Correct Company Name],
                "_1"SUM ( Database[Amount] )
            ),
            NOT ( ISBLANK ( CompanyNameLookup[Correct Company Name] ) )
        ),
        [_1] >= 100000
    ),
    [_1]
)

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Super User IV
Super User IV

Please try this expression instead.  You could also use the Visual Level filter for your table and uncheck the Blank values from the Correct Company Name column.

 

100k+ (SUMMARIZE) =
SUMX (
    FILTER (
        FILTER (
            SUMMARIZE (
                Database,
                'Class Lookup'[Class],
                CompanyNameLookup[Correct Company Name],
                "_1"SUM ( Database[Amount] )
            ),
            NOT ( ISBLANK ( CompanyNameLookup[Correct Company Name] ) )
        ),
        [_1] >= 100000
    ),
    [_1]
)

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

dedelman_clng
Super User II
Super User II

Hi @ESDC -

 

Please try this

 

100k+ (SUMMARIZE) =
SUMX (
    FILTER (
        SUMMARIZE (
            Database,
            'Class Lookup'[Class],
            CompanyNameLookup[Correct Company Name],
            "_1", SUM ( Database[Amount] )
        ),
        [_1] >= 100000 && NOT( ISBLANK( [Correct Company Name] ) )
    ),
    [_1]
)

If that doesn't work, please provide you data in a format that can be copy/pasted into Excel or Power BI, rather than a screen shot.

 

Hope this helps

David




Proud to be a Super User!




Thanks to both of you. I have taken an advanced Dax Course, but the difficulty for me is how to combine multiple Dax Expressions into the correct order to make them work for me. For instance I know I can get my answer by deselecting blank on my filter so I was trying to incorporate it in the Dax, but not sure where it needed to go in my formula. Thanks again!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors