Forum Discussion

Jamie1989's avatar
Jamie1989
Regular Visitor
4 years ago

YTD Sales Variance

Hello all!

 

New user to Power BI and currently working my way through learning!

 

I am stuck at waterfalls at the moment. I am following allow with this guide Create scatter, waterfall, and funnel charts - Learn | Microsoft Docs

 

Within here there is an example of how to use the waterfall, which in itself is easy to create. I am however stuck as so far they havent taught how to crease "YTD Sales Var" which i suspect is what i need to replicate the highs and lows represented in the video linked. As it stands I am seeing the following as I haven't been able to create a quick measure to show sales variance the way I have been reading.

 

Apologies, my DAX is still very early so now entirely sure how to write these myself confidentyl... You can see the data i want to sample is very basic.

 

 

Need Help

1 Reply

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Jamie1989 ,

     

    I think it's showing you how he created the waterfall chart. You are probably more concerned that you only have the green increase in your results and not the red decrease are you?

    Actually, as long as the number given (the number entered into the chart, the value you put in Values) has a negative number, there will be a red drop.

    As for the YTD sales variation, it means the total sales of the year minus the total sales of the previous year. So I wrote the following DAX for your reference.

    YTD Sales Variance =
VAR _varp =
    CALCULATE(
        SUM( 'Sale'[revenue] ),
        FILTER( ALL( 'Date' ), [year] = SELECTEDVALUE( 'Date'[year] ) - 1 )
    )
VAR _varc =
    SUM( Sale[revenue] )
RETURN
    _varc - _varp

    Result:

     

    pbix at the end.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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