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 "...
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:

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!

 

 

 

Barthel's avatar
Barthel
Solution Sage
3 years ago

nonoFRissy85,

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

  • nonoFRissy85's avatar
    nonoFRissy85
    Regular Visitor
    3 years ago

    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's avatar
      Barthel
      Solution Sage
      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:

      • nonoFRissy85's avatar
        nonoFRissy85
        Regular Visitor
        3 years ago

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

        Thanks for your time and help!