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
Icon for Community Champion rankCommunity 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
    Icon for Super User rankSuper User
    6 years ago

    Hi,

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