Forum Discussion

anacerco's avatar
anacerco
Frequent Visitor
8 years ago
Solved

Grouping Values

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                 ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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's avatar
    anacerco
    Frequent Visitor
    7 years ago

    Hello Yuliana,

     

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

     

     

     

  • anacerco's avatar
    anacerco
    Frequent Visitor
    7 years ago

    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!