Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

PowerBi Dax code to replicate excel sumif

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

 
Need Help
Show and Tell
  • dax's avatar
    dax
    7 years ago

    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

3 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      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.