Forum Discussion
Avoiding 'zero' values in a DAX when you have multiple selected in a filter
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.
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_CalculationChainsaw 0
Chainsaw 417
Chainsaw 0
And then you this column for calculation
4 Replies
- smarthp29
Helper I
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_CalculationChainsaw 0
Chainsaw 417
Chainsaw 0
And then you this column for calculation
- smarthp29
Helper I
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.
- v-lionel-msft
Community Support
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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.