Forum Discussion

Cbishop's avatar
Cbishop
Frequent Visitor
6 years ago

SUMIFS function in Power BI

Hi,   I am stuck on how I can apply the following SUMIFS formula into Power BI   =E2/SUMIFS(E:E,A:A,A2,B:B,B2)*100 Re-worked with column names (=Cost 2020/21 - Plan/SUMIFS(Cost 2020/21 - Plan,Gr...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
6 years ago

Cbishop 

If I'm reading the formula correctly, you want to calculate the percentage of each planned cost over the total planned cost by group and month, right?

if so:

Planned cost over grouped planned cost =
VAR PlannedCost = SUM(table[Cost 2020/21 - Plan])

VAR GroupedPlannedCost = CALCULATE(PlannedCost, ALLEXCEPT(table, Table[group], table[month]))

RETURN

DIVIDE(PlannedCost, GroupedPlannedCost) * 100

 

PS, if you wish to display the value as a percentage, you don't need to mutliply by 100; just format the measure to display a percentage

 

 

  • Cbishop's avatar
    Cbishop
    Frequent Visitor
    6 years ago

    Apologies all, think I've confused matters by giving excel table not Power BI table.

     

    Basically what I need to do is calculate the %age of plan for a different groups, the columsn in my Power BI table are:-

     

    Month
    Cost Centre
    Expense Code
    CC Description
    EH Description
    Description - NEW
    Group
    Divisional Grouping
    CDG
    National Specialty
    Exp Group 2
    I&E
    Exp Group 4
    NHSI Cat 1
    Value

     

    First calculation needs to be a SUM of the value column where EXP Group 2 = EXP Group 2 and National Specialty = National Specialty and Month = Month

     

    This value to then be divided by sum of value column where National Specialty = National Specialty and Month = Month and colum I&E is either 'Pay' or 'Non Pay'

     

    Thanks Craig

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Share the link from where i can download the PBI desktop file and show the expected result very clearly.