KPI Percentage difference - discard empty values

[tonijj]

Hi, 

Question 1: Im trying to create a KPI that shows Percentage difference for two RFPs. 

However, what I want the KPI to do, is to Only compare products that both have in their portfolio and sells. 

In the example below I want the KPI to:
-Disregard iPhone 16 and Windows Phone. As only one store/retailer has that product and price for it. 

-To calculate the percentage difference for all products that both retailers have offered a price for, which are;
--Ipad
--Macbook
--PC

Question 2: Is there a quick way to remove or replace all "0" values as seen for:
--Windows Phone for Walmart, and;
--iPhone 16 Publix

Tried to use "Replace.Text" int the query, but couldnt figure out the correct code. 

I simply want to remove the zeros and leave it totally blank. 

Sample Data - PBIX 

Sample Data - Excel  

[v-priyankata]

Hi @tonijj 
@wardy912 @Elena_Kalina Thanks for your inputs.

I hope the information shared was helpful to you. If your question has been answered, kindly mark the most relevant reply as the Accepted Solution. This small action can make a big difference for others who are looking for the same solution.

[v-priyankata]

Hi @tonijj 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @tonijj 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the relevant solution would be awesome for others who might run into the same thing.

[wardy912 · Accepted solution]

Hi @tonijj 

Question 1:

try this measure

KPI_PercentageDifference (%) :=
AVERAGEX(
    FILTER(
        Products,
        Products[WalmartPrice] > 0 &&
        Products[PublixPrice] > 0
    ),
    ABS(Products[WalmartPrice] - Products[PublixPrice]) /
    DIVIDE((Products[WalmartPrice] + Products[PublixPrice]), 2)
) * 100

You could also do this in power query, setting up a calculated column to show 'true' if both values are present, 'false' if one is missing, then filter false

= if [WalmartPrice] = null or [PublixPrice] = null or [WalmartPrice] = 0 or [PublixPrice] = 0 then true else false

Question 2:

Power query is the easiest place to replace values, select 'transform data', go to the table you need, right click the column and select 'replace values'.

If this helps please give a thumbs up and mark as the solution, thanks.

[Elena_Kalina]

Hi @tonijj 

Try to do the following

Question 1:

Create a measure that calculates the percentage difference only for products with prices in both retailers (ignoring iPhone 16 and Windows Phone)

Filtered Variance % = 
// Identify products with prices in BOTH retailers
VAR ProductsWithBothPrices =
    FILTER(
        SUMMARIZE(
            'YourTable',
            'YourTable'[Article],
            "TotalPublik", SUM('YourTable'[Price Publik]),  // Aggregates raw Price Publik
            "TotalWalmart", SUM('YourTable'[Price Walmart]) // Aggregates raw Price Walmart
        ),
        [TotalPublik] > 0 && [TotalWalmart] > 0  // Filters products with non-zero prices
    )
// Calculate average variance for valid products
RETURN
    IF(
        COUNTROWS(ProductsWithBothPrices) > 0,
        AVERAGEX(
            ProductsWithBothPrices,
            DIVIDE([TotalWalmart] - [TotalPublik], [TotalWalmart], 0)  // % difference
        ),
        BLANK()  // Returns blank if no common products exist
    )

---

Question 2:

1. Go to Transform Data → Power Query Editor.

2. Select columns Price Publik and Price Walmart.

3. Right-click → Replace Values:

   • Value to Find: 0

   • Replace With: Leave empty (to set as null).

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[tonijj]

@Elena_Kalina 

First of all, thank you for the help!!

Question 1: 
It doesnt seem to calculate the correct variance. See attached screenshot 1.

Table LEFT = All products available. 
Table RIGHT = Where I removed the products with 0 as value, basically, this is the table that the KPI should use and take into consideration. 

So, the total difference would be: 300.

300 / by Walmart = % of total difference 

Question (followup):
Assuming we can sort out the kinks here, will the formula be dynamic in the sense that If I add "ProductGroup" as a parameter in the visual, it will correctly summarize on that level as well? Or Supplier, etc? Just added a simple "Productgroup" into the Xls and PBIX. 

The original solution has a lot of these aggregations of course, but if I get the sample here to work I should be able to implement it to the real solution.  screenshot 2.

Question 2:

Ah, well now I feel stupid, I sure tried "replace values" but tried to leave it blank which didnt work, didnt know that "null" actually removed stuff, thanks a lot, this one is done then! 

I also updated with your solution in the Pbix file in my original post. 

Screenshot 1

Screenshot 2: