Dynamic X Axis based on count of its number of values

[MCosl]

Hello Everyone,

I would like some help creating a graph based on the following sample data:

Store ID	Product ID	Daily Sales $	Stock $
1	A	28	40
1	B	29	42
1	C	27	50
2	A	18	45
2	B	37	45
3	A	30	44
3	B	16	40
3	C	16	42
4	A	28	50
4	B	35	41
5	A	30	48
5	B	35	50
5	C	31	40

I would have the stores on the X axis, in ascending Order vs a Calculated new Measure by Dividing Stocks vs Daily Sales, resulting in a graph similar to this:

The problem is my data set is exponentially bigger with, more importantly, over 800 Stores, resulting in a given scrollbar for the X axis.

I am having trouble finding a way to visualize the X axis in a more compact way, ideally as a count or % of the total given sample of stores while retaining the ascending order. (Consider that I do have other filters, so the numbers of stores should adjust dynamically based on those)

Ideally, I am aiming to obtain a dynamic graph similar to this:

Any help or suggestions is appreciated.

[MCosl]

Hello, 

Using the pbix you shared, I would like to add a Slicer to adjust the graph based on Store size, like this:

But the usual relationship Modeling does not work, so the graph does not adjust the X axis based on the stores that belong in each size Value.

[lbendlin]

I cannot assist you if you are unable to provide usable sample data. I hope someone else can help you further.

[MCosl]

Hello,

I apologize for the late reply.

First of all, thank you for your help, the file you shared indeed solves the issue regarding the ranking I desired for the table.

I do have an additional question, an usual relationship model for the 2 tables does not seem possible with the current setup using the Store ID values, so is there a workaround to adjust the graph based on Store Size or Location Segmented Data?

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[MCosl]

Fair enough, the sample size is indeed to small to make a scrollbar.

I made an ample enough size for it to behave similarly.

Sample.xlsx

[lbendlin]

Still not very useful.  The ratio is all in the same order of magnitude.  Barely registers as a hockey stick chart (reverse pareto)

[MCosl]

I apologize, I do believe the sample I provided pretty much covers my issue, I could add a couple of columns that I would use as Filters depending on the information I wish to see:

Store ID	Product ID	Daily Sales $	Stock $	Location	Store Size
1	A	28	62	Z	M
1	B	29	54	Z	M
1	C	21	57	Z	M
2	A	25	53	Z	S
2	B	21	76	Z	S
3	A	22	66	Z	L
3	B	21	70	Z	L
3	C	23	75	Z	L
4	A	21	65	X	S
4	B	22	70	X	S
5	A	20	61	X	L
5	B	25	58	X	L
5	C	30	77	X	L

Do let me clarify, If I use the Stores Codification as categorical on the X axis, the only problem I have is that there are too many data points so the graph has a scroll bar, which is visually obnoxious.

If I use it as Continuous, The graph becomes compacted but the Order is based on the X Axis by default, resulting in this graph:

And in this case, the order cannot be transfered to the Measured Value on the Y axis through the usual option within it.

If I remain unclear about my query, please do let me know.

[lbendlin]

 I do believe the sample I provided pretty much covers my issue

Your sample will not result in a horizontal scrollbar, and the values have the same order of magnitude.  This doesn't match the issue you are describing.

[MCosl]

Hello,

The problem is when I make the X axis continuous / logarithmic, I lose the ascending order of the graph based on the calculated measure and there is no usual sorting through it, only the following option remain:

[lbendlin]

Please provide sample data that fully covers your issue.

[lbendlin]

make the x axis logarithmic.