Measure with multiple conditions not working

[Helpful_Fun4848]

Hi all,

I created the following measure and it's not working:

CALCULATE(
SUM(vbi_PODataDetails[Total]),
vbi_PODataDetails[IsNET] = FALSE(),
vbi_PODataDetails[Company] <> "0008",
vbi_PODataDetails[SupplierCode] = "1320",
vbi_PODataDetails[SupplierCode] = "1088",
vbi_PODataDetails[SupplierCode] = "4107",
vbi_PODataDetails[SupplierCode] = "19863",
vbi_PODataDetails[SupplierCode] = "4282",
vbi_PODataDetails[SupplierCode] = "1220",
vbi_PODataDetails[SupplierCode] = "14910",
vbi_PODataDetails[SupplierCode] = "1102",
vbi_PODataDetails[SupplierCode] = "20745",
vbi_PODataDetails[SupplierCode] = "10097",
vbi_PODataDetails[SupplierCode] = "6318",
vbi_PODataDetails[SupplierCode] = "9563",
vbi_PODataDetails[SupplierCode] = "1201",
vbi_PODataDetails[SupplierCode] = "1180",
vbi_PODataDetails[SupplierCode] = "1048",
vbi_PODataDetails[SupplierCode] = "1128",
vbi_PODataDetails[SupplierCode] = "6305",
vbi_PODataDetails[SupplierCode] = "7028"
)

The following is what I want to achieve:

1. I need to get the Total where IsNet = False and it's not Company 0008 with the selected Supplier Code (which is what I hope the above measure, say Grand Total, can achieve)

2. I need to breakdown the Grand Total from point no. 1 into 2 separate Sub-Total where I'm using the above measure but filter it with different Branch Numbers (say Sub-Total 1 and 2). There will be 40 Branch Numbers on Sub-Total 1 and 10 Branch Numbers on Sub-Total 2. 

3. I need to calculate how much is the percentage of Sub-Total 1 and 2 out of the Grand Total .

Company, Supplier Code, and Branch Number columns currently are Text column. 

Any help would be much appreciated!!

[selimovd · Accepted solution]

Hey @Helpful_Fun4848 ,

the way you wrote it all of your codes are connected with an AND, but you want an OR.

Try the following measure:

myMeasure =
CALCULATE(
    SUM( vbi_PODataDetails[Total] ),
    vbi_PODataDetails[IsNET] = FALSE(),
    vbi_PODataDetails[Company] <> "0008",
    vbi_PODataDetails[SupplierCode] IN
   {
        "1320",
        "1088",
        "4107",
        "19863",
        "4282",
        "1220",
        "14910",
        "1102",
        "20745",
        "10097",
        "6318",
        "9563",
        "1201",
        "1180",
        "1048",
        "1128",
        "6305",
        "7028"
    }
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Helpful_Fun4848]

@selimovd 

Measure works, but, I still have problem.

I now have 2 measures:

Measure1=

CALCULATE(
SUM(vbi_PODataDetails[Total]),
vbi_PODataDetails[IsNET] = FALSE(),
vbi_PODataDetails[Company] <> "0008"
)

Measure2=

CALCULATE(
SUM(vbi_PODataDetails[Total]),
vbi_PODataDetails[IsNET] = FALSE(),
vbi_PODataDetails[Company] <> "0008",
vbi_PODataDetails[SupplierCode] IN
{
"1320",
"1088",
"4107",
"19863",
"4282",
"1220",
"14910",
"1102",
"20745",
"10097",
"6318",
"9563",
"1201",
"1180",
"1048",
"1128",
"6305",
"7028"
}
)

I'm creating the third measure:

Measure3=

[Measure2] / [Measure1]

Measure3 is filtered by selected Branch Numbers and supposedly showing the percentage but, it's not working. Help is appreciated!

[selimovd]

Hey @Helpful_Fun4848 ,

can you post the result from [Measure2] and from [Measure1]?

Then we can take a look at [Measure3].

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Helpful_Fun4848]

@selimovd 

This could be relevant info, curently the result of Measure 3 = 0.00M%

Not sure why there is M in there, I set Measure 3 as percentage. 

[selimovd]

Hey @Helpful_Fun4848 ,

the M usually means in Millions %. That would explain why the number looks like a zero.

Go to the settings of the visual you are using and make sure that the display units are set to none:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Helpful_Fun4848]

@selimovd 

This does solve to show the percentage, however, the percentage is wrong. 

Measure 1 = $9.37M

Measure 2 = $8.29M

Therefore, Measure 3 should be 88%, currently, Measure 3 = 30.95%

[selimovd]

Hey @Helpful_Fun4848 ,

can you share the file?

Best regards

Denis

[Helpful_Fun4848]

@selimovd 

How exactly do I share the file with you? I don't see I can attached the file in here.

Even if I'm able to attached the file, you won't see the dataset behind the report/file, not sure if you need to see the dataset as well. 

[selimovd]

Hey @Helpful_Fun4848 ,

you have to upload the file to a service like https://www.swisstransfer.com

Why wouldn't I see the dataset behind? Is the file not in import mode?

Best regards

Denis

[Helpful_Fun4848]

@selimovd 

Dataset is Direct Query which can only be accessed through Virtual Desktop. 

You need to see the dataset as well right? 

[selimovd]

@Helpful_Fun4848 ,

yes, that's a problem then.

I don't really understand why you get a different result for Measure3, when Measure 2 and Measure 1 give the correct result.

Can you put all 3 measures in a table and post a screenshot?

[Helpful_Fun4848]

@selimovd 

You mean the result of the measure like this?

Don't worry if solution can't be achieved without checking the dataset, I'll find another way to get the solution. 

[selimovd]

@Helpful_Fun4848 ,

yes, like this. But can you post it in a table? In a table we know that they all have the same filter context. When they are different visuals the filter context could be different.

Best regards

Denis

[Helpful_Fun4848]

@selimovd 

I hope this is what you mean:

[selimovd]

Hey @Helpful_Fun4848 ,

no, I really mean all 3 measures in one table, like this:

This way the filter for all 3 measures is the same.

In your screenshot you can already see that the filter for visual 1 is different from the filter for visual 2. Like this it's unlikely that the result is the same:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Helpful_Fun4848]

@selimovd 

This is without any of those filters. Percentage is correct. Percentage is not correct once I applied all those filters. 

[selimovd]

Hey @Helpful_Fun4848 ,

why do the different measures require different filters?

When you use 2 measures in another measure, they have by default the same filter context. If you want to have a different behavior, you have to use DAX to make that work.

Should we open another topic for that issue? It's heading a little bit away from the original question of the post, that was solved I guess.

Best regards

Denis

[Helpful_Fun4848]

You are correct, that's the main problem and I have created DAX to also inslude those filter in it and it works.

Much appreciated for your help! I'll close this thread. 

[Helpful_Fun4848]

@selimovd 

Measure 1 and Measure 2 is showing the total in terms of $ value. 

Measure 1 = $9M

Measure 2 = $8M