Sales Analysis using Power BI

[Anonymous]

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

[v-juanli-msft]

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.

[amitchandak]

@bibhuashish, you can consider grouped bar visual or line grouped bar visual

[amitchandak]

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

[Anonymous]

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]

Hi @Anonymous 

If you have any problem implementing this solution, feel free to share screenshots or error message partly.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft ,

Thanks a lot for the help and sorry for responding late. I have asked for some more details from the concerned. Once I get the data and play around with it, I can share the details with you.

Warm regards,

Bibhu

[amitchandak]

@Anonymous , rolling, YTD , SUM, AVG, this two much for a single chart.  Can you share sample data in table format? Or a sample pbix after removing sensitive data. I will try

Refer to theme gallery if that give you new idea

https://community.powerbi.com/t5/Themes-Gallery/bd-p/ThemesGallery/