Grouping Values

[anacerco]

Hello.

I need help on this. I want to create group ranges based on the purchase amount of each market. I have this table:

Market  Transaction amount Date

Brazil      BRL 100                   1/8

Brazil      BRL 50                     1/8

Brazil      BRL 180                    2/8

Canada   CAD 25                     1/8

US           USD 10                     1/8

I want to create ranges for each market such as

Brazil Ranges   Count

1-100                   2

100-200               1

Canada Ranges  Count

1-50                     1

The idea is to that I can have a filter for Market so that if I click on BRAZIL it appears the table for ranges for Brazil.

Can anyone help me on this?

Thank you.

[v-yulgu-msft · Accepted solution]

Hi @anacerco,

How do you define the group ranges? Are they pre-defined in data source? I mean there existing a table like below:

After loading into desktop, please locate to Query Editor mode first.

Right click and duplicate the [Range] column and then split the duplicated one.

Save above changes. In report view, please new below measure:

count =
CALCULATE (
    COUNT ( Dataset1[Transaction] ),
    FILTER (
        Dataset1,
        Dataset1[Market] = SELECTEDVALUE ( Dataset2[Market] )
            && Dataset1[Amount] >= SELECTEDVALUE ( Dataset2[Min] )
            && Dataset1[Amount] < SELECTEDVALUE ( Dataset2[Max] )
    )
)

Then, use a Matrix visual to display data.

Best regards,

Yuliana Gu

[anacerco]

Hello Yuliana,

I just found that I am not being able to show the total for this ranges. I would like to have the total for all the ranges and also a column showing the % that each range has on the total, but somehow when I add a new column of count and click on "show as grand total" is not showing anything. Do you know how can I solve this?

thank you!

[anacerco]

Hello Yuliana,

Thanks for your help. I managed to follow your steps and worked!