How to put suppliers into amount brackets/pots

[ergocorp]

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.

[MFelix]

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])))

[ergocorp]

Thanks, 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.

[ergocorp]

I think we are almost there, Miguel. Thank you for your responses and patience.

I'm getting that message. Here is my table:

Any idea where I'm going wrong with the syntax?

[MFelix]

Hi @ergocorp ,

Does the table with the maximun and minimum is called Table?

You need to write the Table in the measure has 'Table' that is the error you are getting.

[ergocorp]

Hi @MFelix ,

It is indeed called Table:

I just had a thought, could it be that there are no defined relationships between Query1 (where the measure is) and Table?

[MFelix · Accepted solution]

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
)

[ergocorp]

Excellent, thank you @MFelix