How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

[JustinDoh1]

I have uploaded my updated (1/30) PBIX file here.

Bottom is original question (1/29/2025):

Previously, I had wrapped one measure called "Average" inside another measure called "decimalFix":

Average = AVERAGEX(

            VALUES('Date'[DateFormat]),

                CALCULATE(

                [ClientD_M1]

                 )

            )

decimalFix =

             if(DISTINCTCOUNT('Date'[DateFormat])=1,

                        format([Average],"0"),

                        format([Average],"0.0")

                )

which made these possible (number with 1 decimal place as well as 0 decimal :

Due to performace issue of "Average" measure, I modified the "Average" measure to "AverageNew" as below:

AverageNew =

AVERAGEX(

    FILTER(

        SUMMARIZE(

            'Census',

            'Date'[DateFormat],

            "ClientD_M1", [ClientD_M1]

            ),

            [ClientD_M1] > 0

            ),

            [ClientD_M1]

)

Now, I am trying to apply the logic of "decimalFix" to "AverageNew" so that on the new visual (matrix), I would like to have both 0 and 1 decimal places.

For now,  each day's number is fine (with 0 decimal), but the 'Average Census' needs to show 1 decimal numbers.

How do I go about doing it?

Update (1/30/2025):

Sorry. I realized that I need to come up with a solution to treat rows with null data (because there is filter to limit data and there are more than 1 hierarchy of data):

Bottom measure would not work for the null data that is not showing in these date period:

decimalFixNew =

if(DISTINCTCOUNT(DimDate1[DateFormat])=1,

format([AverageNew],"0"),

format([AverageNew],"0.0")

)

So, requirement is how to get rid of those rows with blank value?

Thanks.

[bhanu_gautam]

@JustinDoh1 , Use this measure

decimalFixNew =
IF(
DISTINCTCOUNT('Date'[DateFormat]) = 1,
FORMAT([AverageNew], "0"),
FORMAT([AverageNew], "0.0")
)

[JustinDoh1]

@bhanu_gautam  Thank you so much for your help again. Sorry. I realized that my requirement has to be changed because initially, I had an issue with dealing with null value, and I thought placing on the measure "AverageNew" would meet the need. 

            [ClientD_M1] > 0

            ),

            [ClientD_M1]

And I wanted to ask to just deal with 1 decimal place.

Sorry. I presented the scenario which would not meet the issues on initial stage (having multiple hierarchy) yesterday. 

I have updated my content on the top with updated PBIX file attached here.

I realized that I need to come up with a solution to treat rows with null data:

How do I fix so that all of "Insurance" hierarchy would not show up?

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please try creating a new measure like below.

decimalFixNew = 
if(DISTINCTCOUNT('Date'[DateFormat])=1,
format([AverageNew],"0"),
format([AverageNew],"0.0")
)

[JustinDoh1]

@Jihwan_Kim 

Thank you so much for your help again. Sorry. I realized that my requirement has to be changed because initially, I had an issue with dealing with null value, and I thought placing on the measure "AverageNew" would meet the need. 

           [ClientD_M1] > 0

            ),

            [ClientD_M1]

And I wanted to ask to just deal with 1 decimal place.

Sorry. I presented the scenario which would not meet the issues on initial stage (having multiple hierarchy) yesterday. 

I have updated my content on the top with updated PBIX file attached here.

I realized that I need to come up with a solution to treat rows with null data:

How do I fix so that all of "Insurance" hierarchy would not show up?

[Anonymous · Accepted solution]

Hi @JustinDoh1 

Thanks for the reply from Jihwan_Kim and bhanu_gautam .

JustinDoh1 , please try to create a measure as follows.

Measure = IF([decimalFixNew] <> BLANK(), 1, 0)

Put the measure into the visual-level filters, set up show items when the value is 1.

If another user's answer helped you, please mark their answer as the solution. This will help more users who are facing the same or similar difficulties. Thank you!

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JustinDoh1]

@Anonymous FYI, I just created a new post related to performance issue. Thanks!

[JustinDoh1]

@Anonymous I would like to close this thread because I think what I am experiencing is more of performance issue. But, if you are able to help me to come up with a solution to modify the "decimalFixNew" with additional logic, please let me know. Thank you!

[JustinDoh1]

@Anonymous 

Thank you so much for your help.

Even though it works with a small dataset, when I applied into filter on my regular PBIX file (which has a data from year 2022), the visual crashes.

Is there anyway not to use filter but modify/add the logic on "decimalFixNew" by chance? Sorry for trouble..

I am also curious how I could minimize the size or group dataset.

I also experience that when I try on Bookmark, visual crashes more. I am wondering why?

Thanks!

[bhanu_gautam]

@Jihwan_Kim , I didn't saw your response I was drafting one sorry for that

@JustinDoh1 , Go with this both are same