SUMIFS function in Power BI

[Cbishop]

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,Group,Group,Month,Month)*100)

Data table below:-

Group	Month	Exp Group 2	Cost 2020/21 - Actuals	Cost 2020/21 - Plan
Gas	Apr	Clinical Supplies and Services	31,530	53,724
Gas	Apr	Consultants	182,526	210,190
Gas	Apr	Drugs	175,677	251,407
Gas	Apr	Establishment & Transport	2,021	3,264
Gas	Apr	Finance Costs	6,431	6,038
Gas	Apr	Junior Medical	97,306	86,219
Gas	Apr	Non Clinical Staff	73,780	68,768
Gas	Apr	Non Clinical Supplies	1,781	2,619
Gas	Apr	Nursing, Midwifery & HCA	363,692	417,645
Gas	Apr	Other Operating Expenses	70,055	52,771
Gas	Apr	Premises & Fixed Plant	3,009	1,630
Gas	Apr	Purchase of healthcare	14,062	1,155
Gas	Apr	STT	-283	0
Gas	May	Clinical Supplies and Services	32,345	62,255
Gas	May	Consultants	187,249	243,567
Gas	May	Drugs	180,222	291,329
Gas	May	Establishment & Transport	2,073	3,782
Gas	May	Finance Costs	6,598	6,997
Gas	May	Junior Medical	99,824	99,910
Gas	May	Non Clinical Staff	75,689	79,688
Gas	May	Non Clinical Supplies	1,828	3,035
Gas	May	Nursing, Midwifery & HCA	373,103	483,964
Gas	May	Other Operating Expenses	71,867	61,151
Gas	May	Premises & Fixed Plant	3,087	1,889
Gas	May	Purchase of healthcare	14,426	1,338
Gas	May	STT	-290	0
Sur	Apr	Clinical Supplies and Services	72,141	83,596
Sur	Apr	Consultants	282,243	327,062
Sur	Apr	Drugs	337,590	391,197
Sur	Apr	Establishment & Transport	4,383	5,079
Sur	Apr	Finance Costs	8,108	9,395
Sur	Apr	Junior Medical	115,775	134,159
Sur	Apr	Non Clinical Staff	92,342	107,005
Sur	Apr	Non Clinical Supplies	3,517	4,075
Sur	Apr	Nursing, Midwifery & HCA	560,814	649,868
Sur	Apr	Other Operating Expenses	70,861	82,113
Sur	Apr	Premises & Fixed Plant	2,189	2,536
Sur	Apr	Purchase of healthcare	1,551	1,797
Sur	Apr	STT	0	0
Sur	May	Clinical Supplies and Services	51,362	81,560
Sur	May	Consultants	297,340	472,159
Sur	May	Drugs	286,182	454,439
Sur	May	Establishment & Transport	3,292	5,228
Sur	May	Finance Costs	10,477	16,636
Sur	May	Junior Medical	158,514	251,711
Sur	May	Non Clinical Staff	120,189	190,853
Sur	May	Non Clinical Supplies	2,902	4,608
Sur	May	Nursing, Midwifery & HCA	592,464	940,798
Sur	May	Other Operating Expenses	114,121	181,217
Sur	May	Premises & Fixed Plant	4,902	7,785
Sur	May	Purchase of healthcare	22,908	36,377
Sur	May	STT	-460	-731

Many thanks in advance for any help

Craig

[Cbishop]

Hi All,

Unfortunately all the suggestions didn't complete the required final result and as such I am unable to accept one as a solution.

I have managed to find a workaround to adhere to tight deadlines and will be revisiting this in due course.

Best Regards

Craig 

[Anonymous]

Hi @Cbishop 

Could you share your workaround and mark it as the solution? More people will benefit from it. 

Best Regards,

Rico Zhou

[Anonymous]

Hi @Cbishop 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

Hi @Cbishop 

Just like AllisonKennedy and  PaulDBrown replied before we can achieve the goal by measure.

I build a table like yours in power bi to have test.

I build a measure to calculate Cost2020/21-Plan / sum{Cost2020/21-Plan (group = group month = month) }*100:

Measure =
VAR _A =
    SUM ( 'Table'[Cost 2020/21 - Plan] )
VAR _B =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Group] = MAX ( 'Table'[Group] )
                && 'Table'[Month] = MAX ( 'Table'[Month] )
        ),
        'Table'[Cost 2020/21 - Plan]
    )
RETURN
    DIVIDE ( _A, _B ) * 100

Result:

If you want to calculate other like sum value filtered by (EXP Group 2 = EXP Group 2 and National Specialty = National Specialty and Month = Month).

You may refer to the formula _B in my measure, like :

_C =
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[EXP Group2] = MAX ( 'Table'[EXP Group2] )
            && 'Table'[Month] = MAX ( 'Table'[Month] )
            && 'Table'[National Specialty] = MAX ( 'Table'[National Specialty] )
    ),
    Value
)

You can download the pbix file from this link: SUMIFS function in Power BI

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Best Regards,

Rico Zhou

[PaulDBrown]

@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]

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]

Hi,

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

[AllisonKennedy]

@Cbishop

Generally speaking, you can achieve something similar to a SUMIFS using
Measure = Table[Cost 2020/21 - Plan] / SUMX(FILTER(ALL(Table), Table[Group] = SELECTEDVALUE{Table[Group]) && Table[Month] = SELECTEDVALUE(Table[Month]) ), Table[Cost 2020/21 - Plan] )

You could also look into using ALLEXCEPT in this case too as an alternative option.

[amitchandak]

@Cbishop ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
refer if this cane help

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390
Appreciate your Kudos.

[Cbishop]

@amitchandak 

I need to take the value in column E and divide by the sum of Column E where the Group & month match, so on the first line of data, take the £53,724 divided by the sum of column E where column A = 'Gas' and column B = Apr