Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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!
Hello Yuliana,
Thanks for your help. I managed to follow your steps and worked!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |