cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oneill1 Frequent Visitor
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


Group Weightings - Tables.jpgGroup Weightings - Visuals.jpg

 

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])
    )

Group Weightings 2 - Tables.jpgGroup Weightings 2 - Visuals.jpg

 

 

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.

Supplier Group Spend (excel).jpgSupplier Spend (excel).jpg

 

All source files are located here.

2 REPLIES 2
Community Support Team
Community Support Team

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

Hi @Oneill1 ,

 

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

Oneill1 Frequent Visitor
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

Objective.jpg

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.

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 284 members 3,112 guests
Please welcome our newest community members: