KPI % Comparing Suppliers - Same measure shows different values

[tonijj]

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)

    )

[v-dineshya · Accepted solution]

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]

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]

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

[GrowthNatives]

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]

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

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.