topic Re: Need help with DAX to show percentage based on the result from another DAX in DAX Commands and Tips

Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Thu, 19 Aug 2021 14:54:18 GMT

Hi all,

I created the following measure:

Measure 1 =
CALCULATE(
SUM(vbi_PO[Total]),
vbi_PO[SupplierName] = "ABC Company"
)

I need to create Measure 2 where it will give me the percentage depending on the result of Measure 1. So say if the result of Measure 1 between 1 - 100,000 then it is 10%, if it's between 100,001 - 500,000 then it is 20%.

What would be the DAX for Measure 2?

Much appreciated for any help!!

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Thu, 19 Aug 2021 15:05:10 GMT

Hi Helpful_Fun4848

You can try below code:-

Measure2 = IF ( AND ( [Measure] > 1, [Measure] < 100000 ), FORMAT ( 0.1, "0%" ), IF ( AND ( [Measure] > 100001, [Measure] < 500000 ), FORMAT ( 0.2, "0%" ) ) )

Thanks,

Samarth

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Thu, 19 Aug 2021 15:37:31 GMT

Hi Samarth_18

I did try and create the following:

Measure 2 =

IF (AND([Measure 1] > 1000000, [Measure 1] < 5000000), FORMAT (0.16, "0%"),
IF (AND([Measure 1] > 5000001, [Measure 1] < 9000000), FORMAT (0.17, "0%"),
IF (AND([Measure 1] > 9000001, Measure 1] < 12000000), FORMAT (0.18, "0%"),
IF (AND([Measure 1] > 12000001, [Measure 1] < 15000000), FORMAT (0.19, "0%"),
IF (AND([Measure 1] > 15000000, FORMAT (0.20, "0%"))))))
)

And received the following error message:

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Thu, 19 Aug 2021 18:13:34 GMT

Helpful_Fun4848 you can refer below code:-

Measure 2 = IF ( AND([Measure] > 1000000, [Measure] < 5000000), FORMAT (0.16, "0%"), IF (AND([Measure] > 5000001, [Measure] < 9000000), FORMAT (0.17, "0%"), IF (AND([Measure] > 9000001, [Measure] < 12000000), FORMAT (0.18, "0%"), IF (AND([Measure] > 12000001, [Measure] < 15000000), FORMAT (0.19, "0%"), IF (AND([Measure] > 15000000, FORMAT (0.20, "0%")),[Measure]) ) ) ) )

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Thu, 19 Aug 2021 20:21:48 GMT

Samarth_18 code works, however, I'm still having an issue.

I created 5 different measures for 5 different companies using the same code format as shown on your message above. 4 works but 1 and the one that didn't work is the one where the percentage is under the last and highest category which is 20% for more than $15M if I'm using what's on the code on you previous message but for this particular company it is 19% for more than $6M.

Received the following error message:

"Cannot convert value '19%' of type Text to type True/False."

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Fri, 20 Aug 2021 03:40:47 GMT

Could you please share the code what you are trying with the snapshot of error?

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Fri, 20 Aug 2021 15:55:49 GMT

Samarth_18

This is the error message:

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Fri, 20 Aug 2021 15:57:15 GMT

Helpful_Fun4848 Can you please share the code as well

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Fri, 20 Aug 2021 15:59:39 GMT

Samarth_18

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Tue, 24 Aug 2021 17:52:10 GMT

Samarth_18

I'm assuming this is a mystery as to why the other identical 4 codes works but not this one?

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Wed, 25 Aug 2021 05:25:49 GMT

Try this :-

Westburne = IF ( AND ( [Westburne Current $] > 1000000, [Westburne Current $] <= 3000000 ), FORMAT ( 0.16, "0%" ), IF ( AND ( [Westburne Current $] > 3000000, [Westburne Current $] <= 4500000 ), FORMAT ( 0.17, "0%" ), IF ( AND ( [Westburne Current $] > 4500000, [Westburne Current $] <= 6000000 ), FORMAT ( 0.18, "0%" ), IF ( AND ( [Westburne Current $] > 6000000, [Westburne Current $] < 15000000 ), FORMAT ( 0.19, "0%" ), BLANK () ) ) ) )

if you still got error then kindly share your PBI file after removing sensitive data.

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Wed, 25 Aug 2021 20:22:38 GMT

Samarth_18

It works, much thx! One issue though: I put the result on the table along with the other 4 codes where the result from all 4 are being aligned to the right while the result from the last code is being aligned to the left. Is there a way to change the alignment?

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Thu, 26 Aug 2021 04:26:27 GMT

You can set alignment for specific column through field formatting option in the "Format Pane". You can select field which you want to align and then select respective alignment.

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Thu, 26 Aug 2021 20:48:30 GMT

Samarth_18

There are 4 columns on the table and when I change the alignment, it only affect the first column. The code that we were discussing previously is on the last column.

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Fri, 27 Aug 2021 04:23:10 GMT

Could you please share screenshot what you are referring, it helpful to understand your issue.

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Mon, 30 Aug 2021 16:39:51 GMT

Samarth_18

Originally, I just want to align the last column to the right, but, aligning all columns to be the same is not something that I'll object. However, the alignment only affect the first column.

Re: Need help with DAX to show percentage based on the result from another DAX

Samarth_18 — Fri, 27 Aug 2021 17:41:11 GMT

You can select last column from western current % column inside field formatting option and then select alignment accordingly. You can refer below screenshot for same

Re: Need help with DAX to show percentage based on the result from another DAX

Helpful_Fun4848 — Fri, 27 Aug 2021 21:32:08 GMT

Samarth_18 got it, much thx!!