Forum Discussion
Helper IIHow to put suppliers into amount brackets/pots
Hi all I was wondering of the community could help. I have a lot of spend data, but let's simplify it. Let's say I have:
| Supplier | Spend |
| a | 4 |
| b | 5 |
| c | 3 |
| d | 5 |
| a | 3 |
| e | 3 |
| c | 1 |
| b | 7 |
| f | 5 |
I've used a simple meaure to add up all the same suppliers, well call that Query1[Sum Up]. What i'm looking to do is count up how many suppliers have spent certain values, and visualise it in a table (or matrix if it comes to that). Let's say it's 0-2, 3-4, 5-6, 7+.
Any ideas would be much appreciated.
Thanks.
HI ergocorp ,
There must not be a relationship between this two tables, you just need to add the notation quote to the table name change your measure to:
Total Suppliers by tier = COALESCE ( COUNTROWS ( FILTER ( SUMMARIZE ( 'Query1', 'Query1'[Supplier Name], "@TotalValue", [Sum of Spend] ), [@TotalValue] <= MAX ( 'Table'[Max] ) && [@TotalValue] >= MIN ( 'Table'[Min] ) ) ), 0 )
8 Replies
- MFelix
Super User
Hi ergocorp ,
You have several options one is to create the tiers based on the groups, rigth click the column you need and add new group:
In this window you can make the groups has you need.
Other option is to create a table with the groups you need and a maximum and minimum value similar to this:
ID Group Max Min 1 0 -2 2 0 2 3 - 4 4 3 3 5 - 6 6 5 4 7+ 999 7 Now create the following measure:
Total Suppliers by tier = COUNTROWS(FILTER(ALL('Table'), 'Table'[Spend] <= MAX(Tiers[Max]) && 'Table'[Spend] >= MIN(Tiers[Min])))
ergocorpThanks, I imagine this would work if you have lots of singular data, however I have situations where I have the same supplier. In these instances, the amount would be added up. Taking my original example:
a 4 b 5 c 3 d 5 a 3 e 3 c 1 b 7 f 5 After the SUM measure would effectively be
a 7 b 12 c 4 d 5 e 3 f 5 I would then need to put these into pots/tiers. As that SUM is generated from a measure, it's not the data i need. To overcoe this, would that SUM function be some where in the DAX code?
On a side note, i'm in the dark around new tables. I can see the 'new table' facility, it opens a DAX line for table =
If that is the route I end up with, will I be abl to find how to do it with a simple google search?
- MFelix
HI ergocorp
Redo your measure to the following code:
Total Suppliers by tier = COALESCE ( COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[Supplier], "@TotalValue", SUM ( 'Table'[Spend] ) ), [@TotalValue] <= MAX ( Tiers[Max] ) && [@TotalValue] >= MIN ( Tiers[Min] ) ) ), 0 )Replace the SUM(Table[Spend]) by your measure name.