Forum Discussion

jppuam's avatar
jppuam
Icon for Helper V rankHelper V
6 years ago
Solved

Count metric into intervals

Hello,

i'm trying to count the metrics that fits the intervals correspondent.

For ex: i've a customer table with intervals of growth:

top clients : >40%

medium clients : >20% and <40%

low clients : >5% and <20%

bad clients : <5%

 

and i've another table that has the clients ID and the value of growth.

 

I want to make a bar chart with the intervals field in the X Axis, and i want to count the number of clients with the % growth in each interval.

 

How can i do this ?

 

Thank you,

jppuam

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    @jppuam

    Is this what you want?

    Column = SWITCH(true(),'Table (2)'[value of growth]>0.4, "top clinets",'Table (2)'[value of growth]>0.2,"middle clinets",'Table (2)'[value of growth]>0.05,"low clients","bad clinets")

    1.png2.PNG

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi jppuam 

    If you want to achieve your goal by measure, you may try my way.

    I build two tables like yours Customer Table and Growth Table.

    Measure:

     

    Measure = 
VAR _bad =
    CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Growth] <= 0.05 ) )
VAR _low =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', 'Table'[Growth] > 0.05 && 'Table'[Growth] <= 0.2 )
    )
VAR _medium =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', 'Table'[Growth] > 0.2 && 'Table'[Growth] <= 0.4 )
    )
VAR _top =
    CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Growth] > 0.4 ) )
RETURN
    SWITCH (
        MAX ( 'Custom Table'[Category] ),
        "bad clients", _bad,
        "low clients", _low,
        "medium clients", _medium,
        _top
    )

     

    Then build a bar chart or a column chart, add Interval of growth into Axis , Category into Legend and Measure into Value.

    Result is as below.

    You can download the pbix file from this link: Count metric into intervals

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    @jppuam

    Is this what you want?

    Column = SWITCH(true(),'Table (2)'[value of growth]>0.4, "top clinets",'Table (2)'[value of growth]>0.2,"middle clinets",'Table (2)'[value of growth]>0.05,"low clients","bad clinets")

    1.png2.PNG

    • jppuam's avatar
      jppuam
      Icon for Helper V rankHelper V
      6 years ago

      Hello Amittchandak,

      i'm continuing not understand how to do it.

      Can i sent you an email with the pbix and try to explain better what i need to do ?

       

      thank you,

      jppuam

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi jppuam 

    If you want to achieve your goal by measure, you may try my way.

    I build two tables like yours Customer Table and Growth Table.

    Measure:

     

    Measure = 
VAR _bad =
    CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Growth] <= 0.05 ) )
VAR _low =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', 'Table'[Growth] > 0.05 && 'Table'[Growth] <= 0.2 )
    )
VAR _medium =
    CALCULATE (
        COUNT ( 'Table'[ID] ),
        FILTER ( 'Table', 'Table'[Growth] > 0.2 && 'Table'[Growth] <= 0.4 )
    )
VAR _top =
    CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( 'Table', 'Table'[Growth] > 0.4 ) )
RETURN
    SWITCH (
        MAX ( 'Custom Table'[Category] ),
        "bad clients", _bad,
        "low clients", _low,
        "medium clients", _medium,
        _top
    )

     

    Then build a bar chart or a column chart, add Interval of growth into Axis , Category into Legend and Measure into Value.

    Result is as below.

    You can download the pbix file from this link: Count metric into intervals

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.