Forum Discussion

tonijj's avatar
tonijj
Icon for Helper IV rankHelper IV
1 year ago
Solved

KPI % Comparing Suppliers - Same measure shows different values

Hi,

I struggle with a specific KPI and my DAX formula. I thought I had it but I had to cave and ask for help. I really tried a lot of different approaches, except the right one then obviously... šŸ™‚ 

 

What I want to do:

-Create a KPI that shows the Percentage difference between two suppliers' prices

 

What I have:

  • A Pricing table including both suppliers
  • All prices per Article No
  • Simple calculated column that calculates difference of the two suppliers, but in Local Currency
  • As Supplier A might not have all articles as Supplier B has, there are blanks.
  • I have removed all blanks (In Query) so the Pricing table Only shows Prices when both Supplier A and B has an actual price for that item.

 

 

Problem

  • The KPI, ā€œPercentage differenceā€ doesnā€™t show the correct values. Not in summarizing, nor on row level. It needs to be both as I need to be able to look at individual items AND per total comparing Supplier A and B.
  • Picture below:
    • B is correct. 
    • A is NOT the same value as B. 
    • Both A and B is the same measure. 

 

The Code:

% Diff =
VAR __BASELINE_VALUE = SUM(Cost A)
VAR __VALUE_TO_COMPARE = SUM(Cost B)
RETURN
    IF(
        NOT ISBLANK(__VALUE_TO_COMPARE),
        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
    )

 

 

Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi tonijj ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    I have created sample data based on your screenshot. Please refer below DAX measure to calculate the Weight Diff %.

     

    % Diff Weighted =
    VAR RowDiffs =
        ADDCOLUMNS(
            VALUES('Table'[Supplier]),
            "@Diff",
                VAR Base = CALCULATE(SUM('Table'[Cost A]))
                VAR Comp = CALCULATE(SUM('Table'[Cost B]))
                RETURN DIVIDE(Comp - Base, Base)
        )
    RETURN
        AVERAGEX(RowDiffs, [@Diff])
     
     
    Please refer output snap and attached PBIX file.
     

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

6 Replies

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

    Hi tonijj , you can try this DAX code to solve the issue
    & make sure you have removed the blanks in the query 

    DAX
% Diff :=
DIVIDE (
    SUMX ( Pricing, Pricing[Cost B] - Pricing[Cost A] ),
    SUM ( Pricing[Cost A] )
)

     

    ā­Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
    šŸ’”Found it helpful? Show some love with kudos šŸ‘ as your support keeps our community thriving!
    šŸš€Letā€™s keep building smarter, data-driven solutions together!šŸš€  [Explore More]

     
    • tonijj's avatar
      tonijj
      Icon for Helper IV rankHelper IV
      1 year ago

      GrowthNatives 

      Thank you for the help! 

      When using that formula, here is what I get, the measure is called "Dax Diff %" using the dax code above. 

      Any thoughts? 

       

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

        You can try this DAX Command 

        DAX 
DAX % Diff :=
VAR _Start = SELECTEDVALUE ( StartDate[Date] )
VAR _End   = SELECTEDVALUE ( EndDate[Date] )
RETURN
IF (
    NOT ISBLANK ( _Start ) &&
    NOT ISBLANK ( _End ) &&
    _Start <= _End,
    CALCULATE (
        [% Diff],
        KEEPFILTERS ( dimDate[Date] >= _Start ),
        KEEPFILTERS ( dimDate[Date] <= _End )
    )
)

        Just make sure you have these :
        Ensure StartDate and EndDate tables are disconnected.
        Slicers ā†’ single-select mode.
        Visual ā†’ uses fields from dimDate (not StartDate/EndDate).
        If you still see blanks, confirm your fact table actually has rows whose dimDate[Date] falls between the chosen start/end dates.

         



  • v-dineshya's avatar
    v-dineshya
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi tonijj ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    I have created sample data based on your screenshot. Please refer below DAX measure to calculate the Weight Diff %.

     

    % Diff Weighted =
    VAR RowDiffs =
        ADDCOLUMNS(
            VALUES('Table'[Supplier]),
            "@Diff",
                VAR Base = CALCULATE(SUM('Table'[Cost A]))
                VAR Comp = CALCULATE(SUM('Table'[Cost B]))
                RETURN DIVIDE(Comp - Base, Base)
        )
    RETURN
        AVERAGEX(RowDiffs, [@Diff])
     
     
    Please refer output snap and attached PBIX file.
     

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

    • v-dineshya's avatar
      v-dineshya
      Icon for Community Support rankCommunity Support
      1 year ago

      Hi tonijj ,

      We havenā€™t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

       

      Regards,

      Dinesh

      • v-dineshya's avatar
        v-dineshya
        Icon for Community Support rankCommunity Support
        1 year ago

        Hi @tonijj ,

        We havenā€™t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

         

        Regards,

        Dinesh