Creating Matrix of Calculated Value Ranges

[nkrajacic]

Hello,

I am attempting to replicate a matrix similar to that below which calculates a vendor's spend for a specified time period and then classifies the suppliers into spend ranges. I believe I need to create a calculated column but I am not certain of how to do so for the ranges. Greatly appreciate any guidance. 

[PaulDBrown]

@nkrajacic 

Hi, let me propose an alternative solution using a disconnected table to establish the ranges.

In this example I'm using a simple sales by product model.

Here is a screenshot of the sales table:

First you create a new table in your model with the "Enter Data" option. In this table you have a column for your range definition, the minimum and max threshold for each range and an index column (for sorting purposes).

The table should look like this:

 Make sure that no relationships are created:

Next create the measures which refere to the range table like this:

1) Count the number of products in each range

Number of Products = 
VAR MINSelThresh = LOOKUPVALUE(Ranges[MinThresh]; Ranges[Range]; MIN(Ranges[Range]))
VAR MAXSELThresh = LOOKUPVALUE(Ranges[MaxTheshold]; Ranges[Range]; MAX(Ranges[Range]))
Return
COUNTROWS(
    FILTER(VALUES(Sales[Product]); 
        [Sum of sales] > MINSelThresh && [Sum of sales] < MAXSELThresh))

2) and the sales within each threshold

Total Sum of Sales in Range = 
VAR MINSelThresh = LOOKUPVALUE(Ranges[MinThresh]; Ranges[Range]; MIN(Ranges[Range]))
VAR MAXSELThresh = LOOKUPVALUE(Ranges[MaxTheshold]; Ranges[Range]; MAX(Ranges[Range]))
Return
CALCULATE([Sum of sales];
FILTER(VALUES(Sales[Product]); 
[Sum of sales] > MINSelThresh && [Sum of sales] < MAXSELThresh))

These measures will not give you the grand total; you will need to use SUMX

1) Products in each range with totals

Total Products (Range) = SUMX(Ranges; [Number of Products])

2) Sum of sales with totals:

Total Sum of Sales (Ranges) = SUMX(Ranges; [Total Sum of Sales in Range])

To give you finally:

Which you will then be able to filter using a slicer:

Anyway, just wanted to help with an alternative.

[JarroVGIT]

Can you share your data model perhaps and maybe even some test data? 

I am thinking of creating a calculated table like this: (note: make sure the Ranges column is ordered by Index in the Modeling ribbon -> Sort by Column)

RangesTable = DATATABLE("Index", INTEGER, "Ranges", STRING, "Min", DOUBLE, "Max", DOUBLE,
{
    {1, "$1K - $24K", 1000.00, 24999.99},
    {2, "$25K - $49K", 25000.00, 49999.99},
    {3, "$50K - $99K", 50000.00, 99999.99},
    {4, "$100K - $499K", 100000.00, 499999.99},
    {5, "$500K - $999K", 500000.00, 999999.99},
    {6, "$1MM+", 1000000.00, BLANK()}
})

Next is to create a Measure like this:

# Vendors = 
VAR _curMin = SELECTEDVALUE(RangesTable[Min])
VAR _curMax = IF(SELECTEDVALUE(RangesTable[Max]) = BLANK(), MAX(TableTest[Ammount]), SELECTEDVALUE(RangesTable[Max]))
VAR _filteredTable = FILTER(TableTest, TableTest[Ammount]>= _curMin && TableTest[Ammount] <= _curMax)
RETURN
COUNTROWS(_filteredTable)

Or for total spend:

Total Spend = 
VAR _curMin = SELECTEDVALUE(RangesTable[Min])
VAR _curMax = IF(SELECTEDVALUE(RangesTable[Max]) = BLANK(), MAX(TableTest[Ammount]), SELECTEDVALUE(RangesTable[Max]))
VAR _filteredTable = FILTER(TableTest, TableTest[Ammount]>= _curMin && TableTest[Ammount] <= _curMax)
RETURN
SUMX(_filteredTable, [Ammount])

In my incomplete test data set, this results in the following table:

Well this was a fun one to answer, hope it helps your use case. If you like the post, please hit that thumbs up 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[nkrajacic]

Thanks @JarroVGIT - this was very helpful and I am almost at my solution however, the # of vendors being returned is inflated because the COUNTROWS calculation is returning the total number of rows in my dataset and there are multiple rows of spend data for many of the vendors. Is there a way to subtotal (or similar) spend by vendor and then COUNT? For example, the calculation is returning a count of 5 for vendor with >$1MM spend however there are actually only 2 vendors with spend >$1MM (1 of the vendors has 4 rows of spend >$1MM). 

[JarroVGIT · Accepted solution]

Try replacing COUNTROWS(_filteredTable) with COUNTROWS ( DISTINCT ( SELECTCOLUMNS ( _filteredTable, "col", [VendorColumnName] ) ) )

[nkrajacic]

@JarroVGIT This worked. Thanks much, really appreciate it.

[nkrajacic]

@JarroVGIT I actually may have jumped the gun - this identified distinct suppliers for each threshold but it doesn't seem to be adding up individual rows for each supplier. Is there some sort of subtotal function that would accomplish?

[JarroVGIT]

Ah yes, I see where this is going wrong. I assumed a Vendor would only occur once in the dataset. Per my testing, this should work for your measures:

# Vendors = 
VAR _summaryTable = SUMMARIZE(ALL(TableTest), TableTest[Vendor], "TotalAmmount", SUM(TableTest[Ammount]))
VAR _curMin = SELECTEDVALUE(RangesTable[Min])
VAR _curMax = IF(SELECTEDVALUE(RangesTable[Max]) = BLANK(), MAXX(_summaryTable, [TotalAmmount]), SELECTEDVALUE(RangesTable[Max]))
VAR _filteredTable = FILTER(_summaryTable, [TotalAmmount] >= _curMin && [TotalAmmount] <= _curMax)
RETURN
COUNTROWS(_filteredTable)

Changed: I now create a summary table and filter that on the min and max value of the current range. Then countrows works as every vendor only occurs once in the _filteredTable and has the associated TotalAmmount column. Same principle for the Amount:

Total Spend = 
VAR _summaryTable = SUMMARIZE(TableTest, TableTest[Vendor], "TotalAmount", SUM(TableTest[Ammount]))
VAR _curMin = SELECTEDVALUE(RangesTable[Min])
VAR _curMax = IF(SELECTEDVALUE(RangesTable[Max]) = BLANK(), MAXX(_summaryTable, [TotalAmount]), SELECTEDVALUE(RangesTable[Max]))
VAR _filteredTable = FILTER(_summaryTable, [TotalAmount] >= _curMin && [TotalAmount] <= _curMax)
RETURN
SUMX(_filteredTable, [TotalAmount])

Sorry for the confusion! Hope this helps 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[nkrajacic]

@JarroVGIT So I would use these DAX formulas as my measures instead of the two I was using before?

[JarroVGIT]

Yes, these measures will work with the datamodel like you shared one post ago. The previous ones wouldn't as it expected to have only 1 line per vendor. 🙂

[nkrajacic]

@JarroVGIT my supplier counts are very high now. See DAX below. Any idea what I may have goofed up on...

# Suppliers =

VAR _summaryTable = SUMMARIZE(ALL('BI LOAD TAB'), 'BI LOAD TAB'[Supplier Name], "Total Spend", SUM('BI LOAD TAB'[Spend (USD)]))

VAR _curMin = SELECTEDVALUE(RangeTable[Min])

VAR _curMax = IF(SELECTEDVALUE(RangeTable[Max]) = BLANK(), MAXX(_summaryTable, [Total Spend]), SELECTEDVALUE(RangeTable[Max]))

VAR _filteredTable = FILTER(_summaryTable, [Total Spend] >= _curMin && [Total Spend] <= _curMax)

RETURN

COUNTROWS(_filteredTable)

[JarroVGIT]

Hi @nkrajacic 

Sorry to hear it still isn't working for you. Your DAX is fine, it should work. I have tested it myself with test data. Perhaps you would benefit of having a look at my pbix file?

https://1drv.ms/u/s!Ancq8HFZYL_aiIliJsgQRYrQVSZMEA?e=QYSpv4

The relevant tables are 1RangesTable and 2TableTest and my visual is on Page 2. you can ignore the other tables and pages, these are for other topics in this community 🙂 Let me know if you have any questions!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[nkrajacic]

@JarroVGIT yep, this all makes sense especially after viewing your example. I am very puzzled why my supplier count is still out of whack. Especially since the second DAX which calculates total spend seems to be working just fine. 

[nkrajacic]

@JarroVGIT  is there any way that the measure could be ignoring filters that I have established? The new supplier total looks like it could be very similar to the supplier count for all of the categories in the raw data file. I have it filtered for only my category.

[JarroVGIT]

Ah yes this does ingore external filters, if you don't want that you can remove ALL(0 statement in first variable (in both measures) but leave the tablename there as first argument of SUMMARIZE

[nkrajacic]

BINGO!! We got it! Thanks again!

[nkrajacic]

@JarroVGIT here is a condensed sample of the data. I am trying to aggregate the spend for each of the suppliers before producing the range table output.