Measure or count in Y Axis

[nonoFRissy85]

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?

Thanks for your help!

[nonoFRissy85]

Thank you very much Barthel for your answer!

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!

[Barthel]

@nonoFRissy85,

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?

[nonoFRissy85]

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!

[Barthel]

@nonoFRissy85,

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

[nonoFRissy85]

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)

[Barthel · Accepted solution]

@nonoFRissy85,

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).  

1. Add a parameter table to your model.
   
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:

[nonoFRissy85]

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

Thanks for your time and help!

[Barthel]

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.