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

[Helpful_Fun4848]

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!!

[Samarth_18]

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

[Helpful_Fun4848]

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:

[Samarth_18 · Accepted solution]

@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])
                )
            )
        )
)

[Helpful_Fun4848]

@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."

[Samarth_18]

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

[Helpful_Fun4848]

@Samarth_18 

This is the error message:

[Samarth_18]

 @Helpful_Fun4848 Can you please share the code as well 

[Helpful_Fun4848]

@Samarth_18 

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

[Helpful_Fun4848]

@Samarth_18 

[Samarth_18]

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.

[Helpful_Fun4848]

@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?

[Samarth_18]

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.

[Helpful_Fun4848]

@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. 

[Samarth_18]

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

[Helpful_Fun4848]

@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.  

[Samarth_18]

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

[Helpful_Fun4848]

@Samarth_18 got it, much thx!!