cancel
Showing results for
Did you mean:
Frequent Visitor

## Unique Count of Invoice and Total Product Spend based on Vendor from Top 80% Spend

Dear Community,

I search this forum with many different keywords but still cannot get to solution yet, totally no idea how to tackle this.

The complex part is that the value should be responsive to all present filters, so calculated column cannot achieve the result, believe it has to be dynamic measures.

Sample data like this:

 Vendor InvoiceId Department ProductId Spend Amount Vendor A INV001 Dept1 Product1 10 Vendor A INV001 Dept1 Product2 20 Vendor A INV001 Dept3 Product3 50 Vendor A INV001 Dept4 Product1 40 Vendor A INV002 Dept2 Product1 25 Vendor A INV002 Dept3 Product3 80 Vendor A INV002 Dept3 Product1 100 Vendor B INV003 Dept1 Product1 90 Vendor B INV003 Dept1 Product2 80 Vendor B INV003 Dept3 Product3 70 Vendor B INV003 Dept4 Product1 50 Vendor B INV004 Dept2 Product1 15 Vendor B INV004 Dept1 Product2 25 Vendor B INV004 Dept3 Product3 35 Vendor B INV004 Dept3 Product1 45 Vendor C INV005 Dept1 Product1 90 Vendor C INV005 Dept1 Product1 36 Vendor D INV006 Dept1 Product1 90 Vendor D INV006 Dept1 Product1 150 Vendor D INV006 Dept1 Product1 15 Vendor D INV007 Dept2 Product2 35 Vendor D INV007 Dept3 Product3 20

Desired result:

Visual A is total spend amount by vendor (shows that Vendor B & Vendor A are contributed to Top 80% of spend amount).

Visual B (spend/inv count by product), C1 and C2 (inv count based on amount range) are calculated based on Top 80% Spend Vendors.

I only managed to achieve visual A, but unable get the result for visual B, C1 and C2.

Any help would be very much appreciated.

5 REPLIES 5
Frequent Visitor

Try on this dax but didn't get the correct result. Can anyone helps?

Top Invoice # =
VAR SpendByParentSupplier =
ALLSELECTED ( Data[Vendor]),
"SpendAmount", [Total Spend],
"Vendor1", Data[Vendor]
)
VAR AllSpend =
CALCULATE (
[Total Spend],
ALLSELECTED ( Data[Vendor] )
)
VAR CumulatedPercent =
SpendByParentSupplier,
"Cumulative%",
VAR CurrentSpendAmount = [SpendAmount]
VAR CumulatedSpend =
FILTER (
SpendByParentSupplier,
[SpendAmount] >= CurrentSpendAmount
)
VAR CumulatedSpendAmount =
SUMX (
CumulatedSpend,
[SpendAmount]
)
RETURN
DIVIDE (
CumulatedSpendAmount,
AllSpend
)
)
VAR FilteredTable =
filter( CumulatedPercent, [Cumulative%] <= 0.8)

VAR SpendbyInv =
ALLSELECTED ( Data[Vendor], Data[ProductId],Data[InvoiceId]),
"SpendAmount1", [Total Spend],
"Vendor1", Data[Vendor]
)
VAR FilteredTable2 =
FILTER(
CROSSJOIN(
SpendbyInv,
'Range'),
[SpendAmount1] >='Range'[Min Value] &&
[SpendAmount1] < 'Range'[Max Value]
)

Var JoinedTable =
NATURALINNERJOIN(
FilteredTable,
FilteredTable2)

RETURN
Calculate([Invoice Count],  KEEPFILTERS( JoinedTable ))

Super User

Your question is an variant of common Pareto analysis; but I'm not surprised that you can't solve it after a deep look into it. Its intricacy is way more than it appears. It's way beyond most users.

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Frequent Visitor

I gather Dynamic ABC Classification from this post might be helpful but I just don't know how to fit it to my case.
ABC classification – DAX Patterns

Solution Sage

Can you enlighten us how you got Visual B ???, especially Spent Amount(Top 80% Spend Vendors) more explicitly.

Frequent Visitor

Hi, thanks for replying to my question.

Vendors are ranked/sorted based on total spend amount, from largest total amount ranked as first to smallest. Based on the sorted vendor then calculate the cumulative percentage. Lastly, vendors with cumulative percentage from 0% up to 80% are categorised as Top 80% spend vendors. (this is what you can see from visual A).

From the sample data given above, only vendor A and B are within cumulative 80% (vendor D & C already exceeded 80% so out of scope).

So, visual B is the total amount and invoice count by product for Top 80% spend vendors, which is vendor A and B only. This two vendors contributed 3 products (Product1, Product2 and Product3).

Below is example how to get the total amount (\$375) and invoice count (4 invoices) for Product1.

I hope this helps.

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.