cancel
Showing results for
Did you mean:
Frequent Visitor

## Calucalte weighting totals by supplier, using slicers to split by Supplier groups and divisions.

I have been going around in circles with this for some time now. I read articles regarding using Sumx, calculated columns and measures.

I have set of suppliers that work on a number of contracts where the contract maybe split 80/20 between suppliers.

I am trying to split the total value per supplier, then further by division using slicers

My aim is prodice the following table to display payments split by

1. Supplier (which is a weighted calculation of Supplier Groups)
2. Division

I have the following reference tables

1. Payements (split by division and group name)
2. Supplier Groups weighting (mapping the Supplier group, to suppliers and the weighting i.e. 80% of contract)
3. Supplier Groups (naming convention for supplier groups)
4. Suppliers (naming convention for supplier)

From this i created the following tables, the total is correct at £315,497,417.69 however the supplier or group of suppliers totals arent correct.

I have created 2 versions to work through.

1. I have created table directly from the payments data above, however as expected it bring up the full totals.
2. I have used calculated columns

Then i tried using calculated tables as follows:

```Supplier spend (Group) =

SUMMARIZE('Supplier Groups Weight 1',
'Supplier Groups Weight 1'[Group Name],
'Suppliers'[Supplier Name],
'Supplier Groups Weight 1'[Weight],
"Payments", SUM('Division Total'[Payments]),
"Total", MIN ('Supplier Groups Weight 1'[Weight]) * SUM('Division Total'[Payments])
)```
```Division Total =

SUMMARIZE('Payments',Payments[Division],
Payments[Group Name],
"Payments",
SUM(Payments[Payment Amount]),
"Total", MIN ('Supplier Groups Weight 1'[Weight]) * SUM(Payments[Payment Amount])
)```

I have created the following reference table within Excel to use to validate. I am unable to get the slicers to work across divisions, suppliers and supplier groups.

All source files are located here.

2 REPLIES 2
Community Support Team

Hi @Oneill1 ,

Best Regards,

Amy

Frequent Visitor

## Re: Calucalte weighting totals by supplier, using slicers to split by Supplier groups and divisions

Hi Amy

Thank you for the the response.

I will try and summerize / clarify my objective

Files:

1. I have uploaded the anonomised data to OneDrive here

Objective:

Calculate the supplier spend, per division.

This is a calculation of a percentage of a (supplier) Group.

i.e. Supplier 1 = 100% of Group 01 and 80% of Group 16.

We would mainly focus on division 1+2, however would need visibility of spend on the other divisions.

These images would be on seperate reports

The next step would be to weight the performance of the supplier based on the percentage of spend by division. However this query is contained to only showing the supplier spend per division.

Thank you again for the help.

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 284 members 3,112 guests
Recent signins: