cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Measure or count in Y Axis

Hi

i wanted to put in a bar chart

on Y Axis a number of stores

on X Axis  a number of products

and so having the number of stores for each number of products

on X Axis i can put my field "product" that is automatically counted or put a measure with a COUNT function

but it's not possible on the Y Axis, why and how to get around this?

1 ACCEPTED SOLUTION
Solution Sage

Ah thanks, now I get it. I have a possible way how you could present this in a bar chart (not sure if this is the most optimal method).

2. Place the parameter column on the y-axis. This column refers to the number of different products (1, 2, 3, etc)
3. Create a new measure, where you count the number of stores for every number of different products it sells.

``````Nb of stores =
COUNTROWS (
FILTER (
DISTINCT ( 'Table'[Store] ),
VAR _store = 'Table'[Store]
VAR _product =
SELECTEDVALUE ( Parameter[Parameter] )
VAR _products =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product] ),
REMOVEFILTERS ( 'Table' ),
'Table'[Store] = _store
)
VAR _result = _products = _product
RETURN
_result
)
)
​``````

Place the measure on the x-axis and this is your result:

8 REPLIES 8
Regular Visitor

Yes i achieved to have the number of product for each store but i'm suprised the tool doesn't allow to aggregate the stores in a number of store like it does on X axis.

Is there a way to make the number of product or store becoming a categorical variable so i can get the visual i need?

Thanks!

Solution Sage

You can aggregate the stores by placing them on the x-axis and taking the 'count' of them, for example. This is not possible for the y-axis, it must always be categorical. You could place the different products here. The bar chart now shows the number of stores per product:

It is only possible to show the number of both at the same time if they are both on the x-axis:

You could possibly place a shared categorical variable on y-axis. For example a date. This way you show the number of stores and products by date:

If this isn't what you're looking for, could you please send an image of what you're trying to create?

Regular Visitor

Thanks Barthel, here is an exemple to be clearer:

 Store Product A X A Y A Z B X C Y C Z D X D Z

so i would like a visual showing this:

 Nb product Nb store 1 1 2 2 3 1

thanks!

Solution Sage

I don't seem to understand how the second table follows from the first. Could you explain this?

Regular Visitor

yes of course:

there is one store selling one product (B)

there is two stores selling two products (C and D)

there is one store selling three products (A)

Solution Sage

Ah thanks, now I get it. I have a possible way how you could present this in a bar chart (not sure if this is the most optimal method).

2. Place the parameter column on the y-axis. This column refers to the number of different products (1, 2, 3, etc)
3. Create a new measure, where you count the number of stores for every number of different products it sells.

``````Nb of stores =
COUNTROWS (
FILTER (
DISTINCT ( 'Table'[Store] ),
VAR _store = 'Table'[Store]
VAR _product =
SELECTEDVALUE ( Parameter[Parameter] )
VAR _products =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product] ),
REMOVEFILTERS ( 'Table' ),
'Table'[Store] = _store
)
VAR _result = _products = _product
RETURN
_result
)
)
​``````

Place the measure on the x-axis and this is your result:

Regular Visitor

What looked simple wasn't in the end! i'll try your solution with my datas.

Thanks for your time and help!

Solution Sage

Hey @nonoFRissy85,

The y-axis must be a categorical variable. So you can show the number of products per store. Or the number of stores per product. Not the number of stores per the number of products.

You could also show the total number of stores and total number of products at the same time by placing them both on the x-axis. The distinction is then made by means of a legend. But it is just one value for store and one value for products.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.