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 wit...
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()) 

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

Hi:

Can you send over example file?

 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi and thanks for it.

      But I think it's still not working properly. I need to compare every brand to Nivea, Nivea should be always 100%, I fixed this, I changed your All Brands measure to 

      "ALL Brands = CALCULATE([Avg Price], Products[Brand]="Nivea")"
       
      But then for the segment "Body" is still calculates wrong - because inside this segment there is only one product - Bodylotion 3 that both of the brands (Loreal & Nivea) have. The index should be 300/150=2 and not 1,43 ( I don't understand where 1,43 is coming from) 
       

       

      BR

      • Whitewater100's avatar
        Whitewater100
        Solution Sage
        4 years ago

        Hi:

        I had explained there was both a brand index and a Loreal index. Maybe you did not see page 2?

        I uploaded the second version prior to your reply. The brand index is a different type of index that I included, useful when you have partial data.

        Anyways I created the Loreal versionthe index vs Loreal looks correct.  Body Lotion 3 is 1 for Loreal, .5 for Nivea and .1 for the other brand, at $2 vs. $20, that looks correct.

         

        The only thing strange is you don't seem to be seeing the latest version?

        https://drive.google.com/file/d/1wAFQbOHxkk7L-L4iH_RSJ61wYYJ4oFZX/view?usp=sharing