Dynamically exclude blank values

[Anonymous]

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,

[Anonymous]

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]

Hi:

Can you send over example file?

[Whitewater100]

Hi:

I made this file. I hope this is what you want. I broke it into a data model which will allow you to do more analysis and use a lot of pre-built in functions. For avg price, there is a test to determine if there is both a price and volume field [total Sales] so it excludes incomplete data.

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

[Anonymous]

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]

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 

[Anonymous]

Hi,

I downloaded the file only once and there was only page 1 in it, I'll have a look at the new file. Thank you. 

[Anonymous]

Hi,

hope it works

https://we.tl/t-SJQG5rwxMv

[Whitewater100]

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:

[Whitewater100]

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..