Forum Discussion

M_OConnor's avatar
M_OConnor
Icon for Helper I rankHelper I
6 years ago
Solved

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. 

 

  • 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

4 Replies

  • 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's avatar
      M_OConnor
      Icon for Helper I rankHelper I

      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's avatar
        smarthp29
        Icon for Helper I rankHelper 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's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity 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 Chen

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