topic Re: DIVIDE Measure Creating Blank Rows in DAX Commands and Tips

DIVIDE Measure Creating Blank Rows

delish68 — Thu, 13 Jun 2024 14:16:00 GMT

I have a calculation item that returns "change" or "growth" by subtracting prior year from current year and then dividing that by prior year. Pretty standard calculation. The problem is that when I add this to my matrix, it returns a bunch of empty rows.

FORMAT (DIVIDE (CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Time Calc] = "YTD" ) - CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Time Calc] = "PYTD" ), CALCULATE ( SELECTEDMEASURE (), 'Time Intelligence'[Time Calc] = "PYTD" )), "Percent")

This is what it looks like without the calculation item:

And this is what it looks like when I add the growth calculation item:

It looks like it has to do with the FORMAT function. If so, is there a way to format this as a percent without the function returning a bunch of blanks and expanding this matrix unnecessarily?

Re: DIVIDE Measure Creating Blank Rows

aduguid — Thu, 13 Jun 2024 14:32:45 GMT

Have you tried using the ribbon to format the measure as a percent?

Re: DIVIDE Measure Creating Blank Rows

delish68 — Thu, 13 Jun 2024 14:41:47 GMT

I don't believe that can be done with Calculation Groups/Items.

Re: DIVIDE Measure Creating Blank Rows

delish68 — Thu, 13 Jun 2024 14:45:20 GMT

This seemed to resolve my issue but not sure if it's the most elegant approach.

Re: DIVIDE Measure Creating Blank Rows

Anonymous — Wed, 19 Jun 2024 04:03:19 GMT

Hi,delish68 Hello,aduguid ,thanks for your concern about this issue.
Very glad to see that you have found a suitable solution and shared it, I think the dax code you have given is easy to understand and is a good solution.
Thank you for sharing it as it will help more users on the forum who have similar problems.
You can mark your own suggestions as solutions.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian

Re: DIVIDE Measure Creating Blank Rows

Anonymous — Thu, 04 Jul 2024 10:25:18 GMT

Hi,delish68 .

If you have found suitable solutions, please share them as it will help more users with similar problems.
Or you can mark the valid suggestions provided by other users as solutions

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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