topic Re: Dax Measure to sort most diverse payment terms in DAX Commands and Tips

Dax Measure to sort most diverse payment terms

Anonymous — Fri, 21 Aug 2020 18:40:49 GMT

Hello. I am trying to create a measure to use in a matrix to sort the most diverse supplier for payment terms. To clarify when i mean most diverse payment terms my goal is to rank suppliers that have more diverse payment terms(by invoice amount weighted). Quick example- i would sort a supplier with 4 payments terms(25% invoice amount each) ranked higher than payment terms also with 4 payment terms but a breakdown of (80%, 10%, 5%,5% for invoice amount totals).

Example:

Supplier Name	Invoice Amount	Payment Terms
A	500	60 Days
A	3400	45 Days
A	3200	30 Days
B	1700	30 Days
B	1700	60 Days
C	1700	45 Days
C	1500	10 Days
C	1700	90 Days
C	1500	60 Days
C	400	30 Days

A Payment Term breakdown: 30 Days-45%,60 Days-7%, 45 Days-48%, 30 Days-45%

B Payment Term breakdown: 60 Days-50%, 30 Days-50%

C Payment Term breakdown: 30 Days-6% , 60 Days-22%, 45 Days-25%, 10 Days-22%, 90 Days-25%

I would rank these suppliers in diversity from C-rank 1, A-rank 2, and B rank 3.

Please let me know if you have any ideas. Any help would be greatly appreciated.

Re: Dax Measure to sort most diverse payment terms

Greg_Deckler — Fri, 21 Aug 2020 18:55:42 GMT

Anonymous - Not sure what the percentages have to do with it, seems like you could do this:

Measure 2 = VAR __Supplier = MAX('Table (8)'[Supplier Name]) VAR __Table = SUMMARIZE(ALL('Table (8)'),[Supplier Name],"Count",COUNTROWS(DISTINCT('Table (8)'))) VAR __Table1 = ADDCOLUMNS(__Table,"Rank",RANKX(__Table,[Count],,DESC)) RETURN MAXX(FILTER(__Table1,[Supplier Name]=__Supplier),[Rank])

PBIX attached below sig, you want Table 8, Page 8

Re: Dax Measure to sort most diverse payment terms

Fowmy — Fri, 21 Aug 2020 19:08:31 GMT

Anonymous

Did not understand this part " i would sort a supplier with 4 payment terms(25% invoice amount each) ranked higher than payment terms also with 4 payment terms but a breakdown of (80%, 10%, 5%,5% for invoice amount totals). "

If you need to rank based on number of payment terms options then how to apply the % that you need to add as a weight:

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

Re: Dax Measure to sort most diverse payment terms

Anonymous — Fri, 21 Aug 2020 19:19:14 GMT

Sorry a little confused on what you mean. Yes i would like to weight payment terms by the invoice amount. I am more concerned with payment terms that are diverse with invoice amounts weighted than just pure count. To clarify my previous section:

IF Supplier A has $100,000 in payment combined, but each payment term(count of 4) each payment term would have a total of $25,000 . I would consider supplier A more diverse than Supplier B that also has $100,000 with 5 payment terms(breakdown 60 days=80,000, 40 days=10,000, 30 days=5,000, 10 Days=4,000, 2 days=1,000) b/c the distribution across payment terms is more spread out compared to Supplier B having 80% of invoices in one payment term category.

Essentially I want to prioritize suppliers that have the most diversity in payment terms by invoice amount. I'm more interested in suppliers that are more divided(invoice amount weighted) than suppliers that have more payment types but only 1 or 2 payments types comprise the majority of invoices.

Re: Dax Measure to sort most diverse payment terms

Anonymous — Fri, 21 Aug 2020 19:21:49 GMT

Hello Greg_Deckler . Thank you for the help. I can seem to get the last line:

 MAXX(FILTER(__Table1,[Supplier Name]=__Supplier),[Rank])

the [Supplier Name] does not seem to work it only allows me to put a table. Additionally I clarified below i wanted to weight payment terms by invoice amount. I posted a clarification below. Any help on how to change the measure to be spend weighted would be much appreciated. Thanks.

Re: Dax Measure to sort most diverse payment terms

Anonymous — Fri, 21 Aug 2020 20:47:59 GMT

Hi there.

I've analyzed the case you've shown and the ranking is different. Most diverse terms has C, then B and then A. Here's a bit of theory.

You say you want to rank them by terms weighted by payments. So, let's consider just one supplier. They have a vector of term-paments: ( (T(1), P(1)), (T(2), P(2)),...,(T(n), P(n)) ).

Weighted Term (by Payment) is defined as:

sum over i (i=1,...,n): T(i) * P(i)
WT = --------------------------------
sum over i (i=1,...,n): P(i)

If you carry out this calculation over the three suppliers, you'll get:

WT(A) = 39.29
WT(B) = 45.00
WT(C) = 50.95

Hence, C > B > A. Which is not what you claim.

Re: Dax Measure to sort most diverse payment terms

Greg_Deckler — Fri, 21 Aug 2020 20:57:01 GMT

Anonymous - Not sure what you are doing, it works in the PBIX I sent, attaching again.

Re: Dax Measure to sort most diverse payment terms

Anonymous — Fri, 21 Aug 2020 22:45:00 GMT

This is an implementation of the algorithm I've pasted before. Have not checked it, so it's your turn.

// Here's a measure that uses the weighted // term (by payment): [Supplier Weighted Terms] = if( HASONEVALUE( T[Supplier Name] ), var __numerator = SUMX( T, T[Invoice Amount] * T[Payment Terms] ) var __denominator = sum( T[Invoice Amount] ) return DIVIDE( __numerator, __denominator ) ) [Supplier Rank] = // 1 means highest [Weighted Terms] if( HASONEVALUE( T[Supplier Name] ), var __suppliersToRankOver = CALCULATETABLE( VALUES( T[Supplier Name] ), ALLSELECTED( T ) ) var __rank = RANKX( __suppliersToRankOver, [Supplier Weighted Terms], , DESC, DENSE ) return __rank )

Please note that the field T[Payment Terms] must be an int, not text, for the above to work.

Re: Dax Measure to sort most diverse payment terms

Anonymous — Fri, 21 Aug 2020 23:08:35 GMT

One more thing... I think the best way to achieve what you want is not to rank by the weighted average term but by the weighted average term spread. That means you should calculate the standard deviation (or absolute standard deviation) of terms where the probabilistic measure would be the proportions of payments. Through this you would really be measuring payment spread, not an averaged term.

Re: Dax Measure to sort most diverse payment terms

Anonymous — Sat, 22 Aug 2020 09:48:42 GMT

// Here's a different measure to rank your // suppliers by. I think it's more relevant // than the weighted average. By the way, // the weighted average is just the expected // value of a random variable (i.e., terms) where // the probabilistic measure is just the proportion // of payments. Of course, it is just another // interpretation. In the same vein, you can // define the SPREAD of the variable with respect // to this probabilistic measure, the spread // being the standard deviation w.r.t. the same // measure. It could also be the absolute standard // deviation. I'll give you formulae for both // interpretations. If you have any background in // probability, you should understand what it // all means. // First, let's calculate the standard deviation // of terms w.r.t. the prob measure of payment // proportions. The below is just a way of // coding the standard deviation: // // |sum over i: P(i) * (T(i))^2 |^(1/2) // |--------------------------- - ([Weighted Avg])^2| // |sum over i: P(i) | // WT Spread = if( HASONEVALUE( T[Supplier Name] ), var __wt = [Supplier Weighted Terms] var __numerator = SUMX( T, T[Invoice Amount] * (T[Payment Terms])^2 ) var __denominator = sum( T[Invoice Amount] ) return SQRT( DIVIDE( __numerator, __denominator ) - (__wt)^2 ) ) // Second, you can calculate the absolute standand // deviation of terms. WT Spread (ABS) = if( HASONEVALUE( T[Supplier Name] ), var __wt = [Supplier Weighted Terms] var __numerator = SUMX( T, T[Invoice Amount] * ABS(T[Payment Terms] - __wt) ) var __denominator = sum( T[Invoice Amount] ) return DIVIDE( __numerator, __denominator ) ) // Both measures above quantify the spread of terms // around [Supplier Weighted Average]. Sinci you want // to have a measure of spread, as you mention, I think // the above should be more relevant. [Supplier Weighted Average] // measures the mean term after which you'll regain all // your payments.