Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dynamically exclude blank values

HI PBI Community, 

 

I have the following issue

My data is structured as followed, I have one column with many different Brands (>100), one column with different products (>500) and one column with prices. I always compare the brands with my main brand (e.g Nivea) - for example for the body lotion 1 - Nivea costs 20, Labello 10 - so Labello / Nivea = 50%, nothing complicated here.

The problem is that for some products only one of the two compared brands has a price - e.g Facecream 2.

How to dynamically exclude this products with blank price values? On brand level  I want to have an index ( Labello/Nivea, Loreal/Nivea....etc)  that considers only those products, where both of the brands have product with prices.

 

 

Thanks and BR,

 

9 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    You can use DIVIDE Function. It skips over missing numerator or denominators.

    Index = DIVIDE[Comp Price], [Nivea Price])

    you can also add your own replacment when a divide by zero causes an error.

    index = DIVIDE[Comp Price], [Nivea Price], [avg price])

     

    Hope this helps..

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hello:

    I'll try to explain by way of an example.

    I have a table. Let's say I want to Divide the start value by the end value to make an index.

    Because I have a couple of zero values , I don't want to include these. So I make two measures for start and end that avoids these 0's.

    Start Amt = CALCULATE(SUM(Divide_Table[Start]), FILTER(Divide_Table, Divide_Table[Start] <> 0 && Divide_Table[End] <> 0))      
    End Amt = CALCULATE(SUM(Divide_Table[End]), FILTER(Divide_Table, Divide_Table[start] <> 0 && Divide_Table[End] <> 0))
     
    Now I can use divide function to ignore any possible zero's and summing errors:
    Final= DIVIDE([Start Amt], [End Amt],0)
     
    the result ignores the incomplete values:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

    thanks for the replays, but it's not this. 

    I've missed an important info in my first post - my bad. I need to weight the prices with the same volume. This is my updated data table:

     

    and then the problem:

     

     

    I tried with calculate sum and sumx and filter the 0 but still the subtotals and totals are wrong - I want to calculate the index (on product, segment and brand level) only for those products that both brands have. 

     

    I need a measure that compares dynamically for every brand in the table, when selected, the price weighted with volume to the price weighted with the same volume of my main brand e.g nivea but only for those product, that both brands have in the same time. Something like a true/false check measure, if (or(product (selected brand) = Blank(), product (nivea)=Black()9, false(), true())