Forum Discussion

nonoFRissy85's avatar
nonoFRissy85
Regular Visitor
3 years ago
Solved

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?

 

Thanks for your help!

Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    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:

8 Replies

  • Barthel's avatar
    Barthel
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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. 

  • nonoFRissy85's avatar
    nonoFRissy85
    Regular Visitor
    3 years ago

    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's avatar
      Barthel
      Icon for Solution Sage rankSolution Sage
      3 years ago

      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's avatar
        nonoFRissy85
        Regular Visitor
        3 years ago

        Thanks Barthel, here is an exemple to be clearer:

         

        StoreProduct
        AX
        AY
        AZ
        BX
        CY
        CZ
        DX
        DZ

         

        so i would like a visual showing this:

         

        Nb productNb store
        11
        22
        31
         

        thanks!