PowerBi Dax code to replicate excel sumif

[Anonymous]

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

[dax · Accepted 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

[Anonymous]

Hi zoe

After looking again it takes the full division total when I introduce slices.

Would I need to create a new calculated table that breaks total by division and group, linking supplier weighting table and then calculate individual supplier totals.

My aim is to be able to slice by division and supplier. Producing a table with the report which displays the groups "contracts" they are receiving payments.

Using a manually created to verify the figures they arent matching.

I read in another post this may not be possible, which has led me down the calculated table theory.

[Anonymous]

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