Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sales Analysis using Power BI

Use case-
We have many automotive parts that are being sold by us. We have grouped them into 2 groups. Top 20 and then rest of the parts
The requirement is that I want to see when we have a good or bad month, which of the top 20 parts are underperforming or overperforming relative to the past average of say 3 months or 6 months or 12 months(This will be driven by a month slicer)

Want help on-
Which visual will be the best to use? and what values should I consider?

I have been trying to use clustered column and line chart but getting lost with how and what values to show

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , you can consider clustered bar visual or line clustered bar visual 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks amitchandak . I have been trying to do that. See the image attached. What I am getting confused about is how to depict this whole relationship in the graph. Columns would be for sales of the part and what about the value of the line? Will it be the simple average of the sales, sum of the sales of each part, or a rolling average of the sales or YTD of sales or MTD sales.

      I appreciate your time and help!!

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Copy the date table to create another date table, the "date2" table has no relationship with others

    date2 = 'Date'

    Create two what-if parameter

    "Last N month" and "TopN"

    Once parameters created, it would create one calculated column and one measure for each parameter

    column-Last N month, measure-Last N month value

    "TopN" and "TopN value"

     

    Create measures

    Measure = CALCULATE(SUM('Table'[sales]),FILTER('Date','Date'[year-month]=SELECTEDVALUE(date2[year-month])))

top n slaes = IF(RANKX(ALLSELECTED('Table'[part]),[Measure],,DESC,Dense)<=[TopN Value],[Measure])

average per part =
IF (
    [top n slaes]
        <> BLANK (),
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[part]
                = MAX ( 'Table'[part] )
                && DATEDIFF (
                    'Table'[date],
                    EOMONTH (
                        TODAY (),
                        -1
                    ),
                    MONTH
                ) <= [Last N month value] - 1
                && 'Table'[date]
                    <= EOMONTH (
                        TODAY (),
                        -1
                    )
        )
    ) / [Last N month value]
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.