Summarize measures only if other column is not null

[gustavo80]

Hi everybody!

I'm stacked with following problem:

Need to summarize measure values from a column, only taking in account the values that are not null in other column for that rows.

In the following screenshot, the number that I need on upper table on the field "total installs should be 263" is 263 ( 143 + 60 ) but I cant achieve.

My two tries was:

total installs should be 263 =

IF ( CALCULATE(SUM( dummy_data[d14]) = BLANK()),

0,

CALCULATE(SUM( dummy_data[installs] )))

total installs should be 263 option b =

CALCULATE(SUMX(dummy_data,

IF(dummy_data[d14] = 0,0,dummy_data[installs] )

)

)

But no one of if works 😞  What am I doing wrong ? I attached mi pbix if it is not clear

.pbix attached:

https://we.tl/t-JaP1Z0B1pe

Thanks in advance,

Regards

[AlB · Accepted solution]

Hi @gustavo80 

If I'm not mistaken, 160+43 is 203, not 263 🙂

Create an additional measure based on the one you have:

total installs should be 263_TOTAL =
SUMX ( DISTINCT ( dummy_data[install_date] ), [total installs should be 263] )

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[gustavo80]

Sorry, my mystake writting the example, obviusly it should be 203, you are right 😁

Thanks, it works perfect! Just to understand .. what was the problem with my formula ? I see you include a distinct to iterate over every day ... my formula was iterating and making SUMX several times for a date, due to my datasource structure ?

Thanks again, regards!

[AlB]

@gustavo80 

You measure checks if  SUM( dummy_data[d14]) = BLANK().  At the total row, that sum is not blank because the whole table is being considered (no filter context), so the measure just does SUM( dummy_data[installs] ). To replicate the behaviour yo have in the visual you need to have all the dates in rows, apply the measure to each row and then sum the result of each row. That is what we do with the new code.

By the way, what is the point of the CALCULATEs in your code?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[gustavo80]

Thanks very clear! My CALCULATE was added in a try to evaluate any row, but for what you said probablly it is not necessary, I will check 

Thanks!

[AlB]

@gustavo80 

You don't need those CALCULATEs. The result will be the same without them.

Earlier I explained the behaviour of the _TOT measure at the total row. I didn't quite explain what happens elsewhere. In any of the other rows of the visual you have one specific date as filter context, so the DISTINCT() will actually return an only row with that particular date. Then your initial measure is invoked on that row and the SUMX  adds only that one result, since we only have one row. You thus get the same result as when applying your measure directly.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers