Forum Discussion

tonijj's avatar
tonijj
Helper IV
1 year ago
Solved

KPI Percentage difference - discard empty values

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...
Need Help
  • wardy912's avatar
    wardy912
    1 year ago

    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's avatar
Elena_Kalina
Solution Sage
1 year ago

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's avatar
    tonijj
    Helper IV
    1 year ago

    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: