Get help in creating a suitable visulization

[Nerdywantocode]

Hi guys, currently I'm having a task to present the number of sale for each product (for example A,B,C..) of each department in my company over 3 most recent months in Power Bi. Could you guys suggest me a visual in which could show those criteria. My desire output is to pdf, in which I have to send to my leader by email, thus using drill interation in PBI is not a good option.

Thanks a lot,

Regards,

[Nerdywantocode]

Hi Guys, thanks all for the solution. Sorry for not giving sample data. Here is sample data look like:

I think i might use a stacked column chart like this. But I'm having trouble in making those column stack with each other for each month. Can someone help me to fix this:

Regards,

[Ritaf1983]

Hi @Nerdywantocode 
If the goal of the graph is to be readable and effective in conveying messages of comparison, a stacked bar chart is a poor choice, to say the least. I have attached a link to 2 articles that explain the problems more or less:

https://medium.com/analytics-vidhya/several-data-interpretation-problems-with-stacked-bar-chart-and-...

https://www.madeiradata.com/post/home-experiments-and-stacked-bar-graphs

my personal recommendation, if you want to go to the columns graph direction, is to use small multiples .
something like these:

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

[Nerdywantocode]

Could you show me the name of the visual 😐

[Ritaf1983 · Accepted solution]

@Nerdywantocode 
This is the functionality of a regular stacked/clustered column graph :

I attached a pbix with one of the examples, you can follow the steps 🙂

[Nerdywantocode]

Nice, will try to convey this to my leader. Thank you 🙂

[Ritaf1983]

Happy to help 🙂

[Uzi2019]

Hi @Nerdywantocode 

What exactly do you want on stack column chart??
 Can you high light something wht do you want ???

[Nerdywantocode]

The column is the number of sales of each product, stacked each other. The x axis represent department sale those product, and then detail for each month. The problem I want those column stick with other  for each department like cluster column chart

[Uzi2019]

Hi @Nerdywantocode 

You can represent this scenario in multiple ways
1) One simple way to represent in a table where absolute value are display

where I have taken Month and product in Row 
Department in Column
Sales in values

2) Dept wise Sales over a Month

3) Tree map but if yoh have less data in Product for each dept

It has Month, Dept, Product with Sales data.
This will not show you month on month trends for thrend analysis try below visual.

4) stacked column chart for month wise trend against different dept

X axis Month
Y axis Sales
Legends Product
Small multiple Dept

I hope this would give you an idea how to present your data using different visual.

I hope I answered your question!

[Nerdywantocode]

Thanks a lot for those suggestions. I prefer a stacked column chart like your 2nd suggestion but I want to present department and sales . Could you see my latest post for more detail 😄

[Uzi2019]

Hi @Nerdywantocode 
Try this chart

Custom column chart

Or try this

or month on month comparison try this chart

I hope I answered your question!

[Nerdywantocode]

Thanks, but how to present the department in the chart ?. I think it's only present product, sale and month, but missing the fourth criteria 😐

[Ritaf1983]

Hi @Nerdywantocode 

It is very difficult to answer such a question without delving into the data itself and a precise specification that includes the exact goals of the requested report and what exactly will be done with each number that appears in it + how many products and departments exactly exist to understand the required layout.

But I will try to give the general direction, the only visualization that allows you to easily see a combination of several metrics and is easy to read is a matrix/combined table in the case you described, probably the metrics will be at the department and product level when the order of the hierarchy between them depends on the main need, whether you check each department separately and measure the products or for each product you check which department has good performance and which department needs to improve.

The metrics:

Total sales for the department (with a bar graph of ranking like in picture 1)
3-month trend line/small columns (like in picture 2)
Maybe the percentage change from the beginning of the measurement/other historical period with alerts using a red circle in case of decreases (picture 3)
And so on as needed...

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

[Nerdywantocode]

Thank you for your idea, but it's look like it's not suitable for a pdf report. I think it's should work well for a dashboard report, but my lead dont want that 😞

[amitchandak]

@Nerdywantocode , if no date is selected you can have a measure like this and use in visual

3 month Today =
var _min = eomonth(today(),-3)+1
var _max = today()
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

3 month not current =
var _min = eomonth(today(),-4)+1
var _max = eomonth(today(),-1)
return
CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

[Nerdywantocode]

Thank you, I might try your idea 😄