Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am looking to calculate
'supplier = (sum of group 1 x weighting) + (sum of group 2 x weighting).
In excel I use 2 tables to calculate, but struggling to figure this out in powerbi so far
I am currently using PowerBi to create Averages of scores per supplier.
I am now trying to moved towards weighted totals and weighted averages.
In my scenario I have
8 Divisions
22 Groups (of suppliers ie. Joint Ventures, multiple Suppliers working on a contract)
8 Individual Suppliers made up of a percentage of a group of suppliers.
Within Excel, I have created a supplier list 1-8 which uses a sum if from another table.
```
SUMIFS('Group Totals (Per)'!F:F,'Group Totals (Per)'!$B:$B,$B2)
```
This other table is a sumif of group name, division multiplied by weighting (percentage of the contract)
```
SUM(SUMIFS(Payments!$I:$I,Payments!$C:$C,'Group Totals'!$A7,Payments!$B:$B,'Group Totals'!F$1)*$C7)
```
My hope is to replicate this within power BI.
So I can click select a supplier and see the total payments received (which will be a sum of a percentage of the group contract (joint ventures).
However I have been unable to separate out the suppliers totals with weighting, after creating links tables.
I have supplied sample data in excel and power BI and screenshots.
Here's a link to the files https://tinyurl.com/y43as4dr
Solved! Go to Solution.
Hi Oneill1,
It seems that you want to calculate result like below
If so, you could try to modify relationship like below
Then create a table like below
table = SUMMARIZE ( 'Supplier Groups Weight', Suppliers[Supplier Name], 'Supplier Groups Weight'[Weight], "payment", SUM ( Payments[Payment Amount ] ), "total", MIN ( 'Supplier Groups Weight'[Weight] ) * SUM ( Payments[Payment Amount ] ) )
Create a measure like below
Measure 3 = CALCULATE(SUM('table'[total]))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Oneill1,
It seems that you want to calculate result like below
If so, you could try to modify relationship like below
Then create a table like below
table = SUMMARIZE ( 'Supplier Groups Weight', Suppliers[Supplier Name], 'Supplier Groups Weight'[Weight], "payment", SUM ( Payments[Payment Amount ] ), "total", MIN ( 'Supplier Groups Weight'[Weight] ) * SUM ( Payments[Payment Amount ] ) )
Create a measure like below
Measure 3 = CALCULATE(SUM('table'[total]))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you so much, this has cracked it.
I had got to a supplier level or division level, but not being able to use a slicer to incorpate the division level. But the mutiple direction relationships appears to have solved that.
Thank you again
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |