Forum Discussion

bhaskarpbi999's avatar
bhaskarpbi999
Icon for Helper V rankHelper V
1 year ago
Solved

Card Visual not showing correct values after excluding dimension column

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

 

 

 

 

 

 

 

 

 

 

 

 

Need Help

32 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    1 year ago
    Revdifftotal = CALCULATE([RevDiff], ALL('Customer'[Customer Name]))// try this might help you
    • bhaskarpbi999's avatar
      bhaskarpbi999
      Icon for Helper V rankHelper V
      1 year ago

      Hi Muhammad.

       

      Thanks for your support. 

      I will try above formula and keep you updated ..

       

      Many thanks in advamce

       

      Regards,

      Bhaskat

      • bhaskarpbi999's avatar
        bhaskarpbi999
        Icon for Helper V rankHelper V
        1 year ago

        Hi Muhammed,

         

        I have tried with calculate function but it's working and below logic is used in measures.

         

        Rev Diff = [Rev CY]-[Rev LY]
         
        Rev CY = 
        CALCULATE(
            [RevRaw],
          MthCal[DateVisCY] = TRUE())
        CALCULATE(
            [RevRaw],
          MthCal[DateVisCY] = TRUE())
         
        Rev LY = 
        CALCULATE(
            [RevRaw],
            MthCal[DateVisCY] = FALSE()
        )    
         
        is there any other alternative solution formula which can be evalautes based on  customer namer  as i am using Rev Diff for other charts and facing same issue with numbers.
         
        Regards,
        Bhaskar

         

         

  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    1 year ago

    bhaskarpbi999 

     

    Hi, could you show how you calculated 'Rev Difference'? It might not be taking Customer Contexts into the calculations.

     

    You may try using VALUES with your Customer Names to create a virtual table of unique customers in the SUMX and see if this issue can be fixed. 

     

    A sample DAX like 

    Rev Diff = 
SUMX(
    VALUES('Customer'[Customer Name]),
    [Current Rev] - [Previous Rev]
)

     

    • bhaskarpbi999's avatar
      bhaskarpbi999
      Icon for Helper V rankHelper V
      1 year ago

      Hi MasonMa.

      Thanks for your support. I will try the suggested solution and keep you updated on the same..

       

      Thanks in advance.

       

      Regards,

      Bhaskat

      • bhaskarpbi999's avatar
        bhaskarpbi999
        Icon for Helper V rankHelper V
        1 year ago

        Hi MasaonMA,

         

        I have used SUMX formula but still the same issue when customer name is escluded from table visual.

        I used the below formulas for Rev Diff and also Rev CY and LY measure.

         

        Rev Diff = [Rev CY]-[Rev LY]
         
        Rev CY = 
        CALCULATE(
            [RevRaw],
          MthCal[DateVisCY] = TRUE())
        CALCULATE(
            [RevRaw],
          MthCal[DateVisCY] = TRUE())
         
        Rev LY = 
        CALCULATE(
            [RevRaw],
            MthCal[DateVisCY] = FALSE()
        )    
         
        is there any other alternative solution as i am using Rev Diff for other charts and facing issue when customer name is not used
         
        Thanks for your grat support.
         
        Regards,
        Bhaskar
         
         
         
         
         
         
         
         
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      MasonMA
      Icon for Super User rankSuper User
      1 year ago

      Anonymous 

       

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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

    • FBergamaschi's avatar
      FBergamaschi
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
        bhaskarpbi999
        Icon for Helper V rankHelper V
        1 year ago

        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