Avoiding 'zero' values in a DAX when you have multiple selected in a filter

[M_OConnor]

Let's say I want to show a bar graph showing Year on Year gross profit for stores broken down into speciic products.

Here's an example of some data I have for three stores. Let's say they sell chainsaws (among other things):

I have used the following logic to create this... Which works in almost every situation other than the bar graph:

Note that due to a way in which I've created a weekly/monthly subset, I need the 'previous year' value to be in the source data, not calculated using 'sameperiodlastyear...

Gross Profit = caulculate(sum(TABLE[Gross Profit This year]), unrelated conditions..)
Gross Profit Last year = caulculate(sum(TABLE[Gross Profit Last year]), unrelated conditions..)

Gross Profit YoY= = SWITCH(TRUE(),
[Gross Profit Last Year] = 0, BLANK(),
[Gross Profit Last Year] <> 0, calculate(([Gross Profit] -[Gross Profit Last Year]) / [Gross Profit Last Year]))

In my bar graph, I've set the visual filter to not show blank YoY (as not doing this causes the bars to ALL go blank).

Here's what I want if I Click Slicer = Store 1, Store 2, Store 3

This is what I get. 

I thought that my DAX code would have caused the YOY value for Stores 1 and 3 to go blank for Chainsaws, but it seems to still include them because store 2 has a value. 

I just want store 2's YoY to show, BUT I want to keep the multi-select functionality, because all stores may have sold batteries and bikes still. Any tips from within Power BI?

Note: I'd rather do it in PBI than adding a YoY column in SQL where possible - It's doable as a last resort but I anticipate some other aspects of the dashboard needing things to be done at a PBI level. 

[v-lionel-msft]

Hi @M_OConnor ,

You try to do it using DIVIDE () function:

Gross Profit YoY = 
DIVIDE(
    [Gross Profit] -[Gross Profit Last Year],
    [Gross Profit Last Year],
    BLANK()
)
    

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smarthp29 · Accepted solution]

@M_OConnor 

What if you created another Gross Profit column which only has value if GP Last Year <>0

IF GP_LY = 0 then 0 else GP

So your 3rd column would be
Product          GP_Calculation 

Chainsaw                    0

Chainsaw                    417

Chainsaw                    0

And then you this column for calculation

[M_OConnor]

@smarthp29  Cheers, I'll try that and use that exclusively when I'm calculating YoY. Will let you know.

Would this column be something in the data source layer or a dax column? This would be fine as it's not the final calculation.

Cheers

[smarthp29]

It would depend on how your data gets refreshed. 

For example, if you get post dated transactions then I would suggest a DAX Calculated Column. If not then getting the column configured in the datasource is always the best option. In case you are unsure, DAX Calculated Column shoudl be implemented to ensure you future proof it.