Want to return BLANK when running total equals total

[ciken]

Hi all - I'm building a running total from the quick measure tool but when the matrix comes across a column where the data would be blank - it's throwing the full value into the column and killing my report. 

EXAMPLE: I am working on a diminishing report of how many times patients order. One order would be the full amount of patients, then 2 would be smaller, then 3 smaller and so on. But, when it gets to a column where no patient exists, it throws the full amount (see chart for example). In 2022, most patients will only have bought 1 or maybe 2 times...but when it populates, 3, 4, 5 etc show the amount of patients enrolled instead of 0.

Can anyone help me figure out what I need to write in the formula to make it appear BLANK() instead of total?

Thanks

DAX: 

Count of Consumer ID running total in Orders on Autoship =

CALCULATE(

    DISTINCTCOUNT('Order Product'[Consumer ID]),

    FILTER(

        ALLSELECTED('Lead'[Orders on Autoship]),

        ISONORAFTER('Lead'[Orders on Autoship], MIN('Lead'[Orders on Autoship]), ASC)

    )

)

 

[PaulDBrown · Accepted solution]

Try:

Count of Consumer ID running total in Orders on Autoship =
IF (
    ISBLANK ( DISTINCTCOUNT ( 'Order Product'[Consumer ID] ) ),
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( 'Order Product'[Consumer ID] ),
        FILTER (
            ALLSELECTED ( 'Lead'[Orders on Autoship] ),
            ISONORAFTER (
                    'Lead'[Orders on Autoship], MIN ( 'Lead'[Orders on Autoship] ), ASC
            )
        )
    )
)

[ciken]

This worked PERFECTLY! It removed all the totals when not necessary and gave me exactly what I needed.

Thanks @PaulDBrown 

[YukiK]

You use functions like HASONEVALUE() or ISFILTERED() with the combination of IF().

Hope this helps!

[ciken]

I'm not sure what the DAX would look like - I tried to create my DAX as a variable and do an IF statement but it either didn't change, or errored out. 

Could you share what it might look like?

Thanks

[YukiK]

Try using HASONEVALUE() like this:

Measure =
VAR YourCalculateLogic = ...

VAR Result = IF(HASONEVALUE(table[Year]), YourCalculateLogicm Blank())

RETURN
Result

Or you can hide the total row in the setting:

 

Consider giving a thumbs up if this is helpful!