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

[Anonymous]

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.

[v-xicai]

Hi @Anonymous ,

I am not sure what desired result would you want, could you please share your desired output screenshots for further analysis? Do mask sensitive data before uploading.

Best Regards,

Amy

[Anonymous]

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.