Card Visual not showing correct values after excluding dimension column

[bhaskarpbi999]

Hi ,

I have an issue with card visual where Rev Difference is not showing correct values after customer name is excluded from table visual.

Correct data

Incorrect data is displayed after removing customer name and now it is showing 30 M instead of 51 million

Could you please suggest if there is any alternative solution or formula to show correct values in card visual.

I am available to proivde further details and thanks for your support

Regards,

Bhaskar

 

[MasonMA]

Hi @FBergamaschi 

Would you have time to look at this question? thanks. 

[FBergamaschi]

This reply is for @bhaskarpbi999 

A quick feedback after checking the file, I hope I am not repeating other supporters argument

The problem disappears is you remove the filter on the value 1 for measure GR FlagM

A filter applied on a measure (that should not be possible) is possible in Power BI becasue it is applied row by row on a visual. Now, if you remove the Customer from the visual, you force the calculation only at the total, including everything (I have removed the filter form the measure, remember)

If I put in The Customer again, you will see that that there are rows where the measure results into 0 but totals are identical to before

Those rows are responsible for the difference you see when you still have the filter on the measure in place for value 1 like this (notice the total decsreases as now the customers for which the measure returns 0 are excluded)

and now you remove the customer: there is no reasos anymore to exclude any customer from the calculation and so you get again 7638 (the filter on the measure is still in place)

That said, why is there that filter on that measure? What is the purpose of this filter? So I think we need to find a different solution to obtain the purpose you will explain, otherwise you will always get different results when you have or not the customer in the visual (in rows) as that defines the granularity at which the measure is calculated and therefore under which condition a customer is excluded (depending on the measure result for that customer, 1 or 0

hope I provided some guidance, I hope to be able to solve this entirely

PS I know it is easier when you get in after many have tried, so if I got something new I do not want to take too much merit. If I simply went back on something already discussed, well I apologize .-)

@MasonMA thanks for calling me in

Please let me know!

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[bhaskarpbi999]

Hi Bergamasch.

I needs GR flagM measure as a slicer filter but we can"t use measure as a filter.

So if I need to use GRFlagM in slicer I have defined that logic.

But my client wants to filter and use RevDiff measure in charts with different dimensions but when use them like month wise and market segment wise not getting right numbers and I have issue to filter growing flag.. Thanks for your extreme support

[FBergamaschi]

It is enough the Force that measure to go one Customer at a time, I will fix it after dinner, ok?

[bhaskarpbi999]

Hi FBergamaschi,

Thanks and kudos for your support 

Regards,

Bhaskar

[FBergamaschi]

Hi @bhaskarpbi999,

can you specify what is the nature of the measure GR Flag M, I need to reproduce that behaviour in another way 

Thanks

[bhaskarpbi999]

Hi FBergamaschi,

I have created GR FLagM bco'z i cant use the measure logic IF([Rev cy] > [Rev Ly], 1, 0) and use it directly in slicer due to power BI feature restriction.

I used GR FlagM measure and filtering it using GR flag slicer which is number column from Flag slicer table and specified the logic in the below formula.

GR FlagM = VAR Test = IF([Rev cy] > [Rev Ly], 1, 0)

RETURN

     IF(ISFILTERED('Flag Slicer'[Number]),

     IF((Test= SELECTEDVALUE('Flag Slicer'[Number])),1,0),0)

When you select GR flag slicer value then only it will display data else no data.

Thanks for your support and if there is a alternative way that would be really helpfull for this issue.

Regards,

Bhaskar

[FBergamaschi]

Is this answer what you were looking for ?

https://community.fabric.microsoft.com/t5/Desktop/Card-Visual-not-showing-correct-values-after-exclu...

If it is, I can share the file so you check in detail

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

Hi @bhaskarpbi999 and @MasonMA,

while I was waiting for a feedback on the neture of the measure used as a filter, I tought to sove it differently

Now I have a file in which I implemented the new logic, where can I upload it so you can test it?

Here how it works now, a few screenshots

The totals stay correct if I remove the Customer:

With the flag on zero:

and still same totals if I remove the Customer

Is this what you were looking for?

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[bhaskarpbi999]

Hi FBergamaschi,

Thanks and kudos for your support.

I have replied about the behavior of GRFlagM but i have seen yourolution  solution post and i am expecting the same result after customer name is removed.

Could you please share the test .pbix so that i can check it and see if it works fine with sclicer filter.

Thanks in Advance for your immense support.

Regards,

Bhaskar

[FBergamaschi · Accepted solution]

yes, here it is

https://drive.google.com/drive/folders/1FHc79P-25eVq8-K2a5c4uoPJ0_5wa6-w?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[bhaskarpbi999]

Hi 

Hi FBergamaschi,

Thanks and kudos for your great support and prompt response.

I am accepting the solution.

Many thanks

Regards,

Bhaskar

Regards,

Bhaskar

Preview

[bhaskarpbi999]

Thanks and kudos for your support.

[FBergamaschi]

Hi @MasonMA,

i can check this but I have no access to the link where the pbix was shared

https://drive.google.com/file/d/1H8NAIdUEQf2mEb6ZpAONztJOX38d9ZDB/view?usp=drivesdk

I hope I get authorized and shall try to help @bhaskarpbi999 can you please authorize me?

meanwhile I try to figure out the issue in all these messages

Best

F

[v-hjannapu]

Hi @bhaskarpbi999,

Thank you for reaching out to the Microsoft fabric community forum.

The issue you are facing is happening because your current Rev Diff calculation is using either VALUES() or a row-level context logic that works fine when Customer Name is part of the visual. But when you remove Customer Name like in a card visual, the calculation behaves differently and results in an incorrect total.

To fix this, you can modify the measure to remove the customer-level filter while still calculating the correct sum. 
I tested it with my sample data, and it worked fine. Please find the attached screenshot and Pbix for your reference.

Hope the above provided information help you resolve the issue, if you have any further concerns or queries, please feel free to reach out to us.
Regards,
Harshitha.

[MasonMA]

@v-hjannapu 

You really should have validated your results before you post it... This is from your file and your 'Rev Diff Correct' measure returns 32M? I'm terrified.. 

[v-hjannapu]

Hi @MasonMA,

Apologies for the confusion earlier, and thank you for pointing it out.

I reviewed the issue again, and you are right the previous explanation was incorrect. @bhaskarpbi999 Please consider the below updated explanation as the correct one.

I have implemented the fix for the Rev Difference measure so that the card visual now calculates the correct total, even when the Customer Name dimension is excluded.

The result matches the sum of the customer-level differences shown in the table visual, as expected.
For your reference, I’m attaching a .pbix file.

Thank you

[bhaskarpbi999]

Hi hjannapu,

Thanks for your support.

i am not using customer name and revenue from same tables.

Customer name from Account parent table

Rev Raw from DWH_ADJ table

And the relationships are like below flow defined path is  DWH_ADJ --> Account --> Accparent

I have tried with slicer but if i remove customer name and the value is not changing as per selection.

Could you please suggest any alternate solution or formual for this issue.

Regards,

Bhaskar

Preview

Preview

[MasonMA]

@bhaskarpbi999 

Hi, based on your pictures, could you enable 'Bi-directional Filtering' ? (even though it's not recommended at model level, it might be a simpler solution. )

What i see from your pictures is when the visual doesn’t include Customer Name, the context from AccountParent doesn’t propagate down to DWH_ADJ table. 

If you wouldnt want to change the filtering direction, you may try with TREATAS as below. Again without looking at your pbix file and data validation, i cannot guarantee it works 100% 

Rev Diff FIXED =
VAR Customers =
    VALUES('AccountParent'[Customer Name])
RETURN
    SUMX(
        TREATAS(Customers, 'AccountParent'[Customer Name]),
        CALCULATE([Rev CY]) - CALCULATE([Rev LY])
    )

Please let me know if it works, thanks. 

[bhaskarpbi999]

Hi MasonMA,

I have implemented the suggested formula but some how it's not working.

The issue when we ahve customer name with GR Flag slicer applied the value is 2169.

After removing customer name the value is 1984 and GR flag slicer also not working

Could you please help if i can modify formula to get the value for Rev CYand LY.

I have prepeared .pbix file with sample data but some how unable to upload file.

Regards,

Bhaskar