topic Re: SUMIFS in DAX in DAX Commands and Tips

SUMIFS in DAX

Anonymous — Thu, 05 Sep 2019 05:42:17 GMT

I also need help with writing a DAX function to work as Sumifs.

I've 3 tables with quality score and max scores and I'm trying to draw a graph which shows overall perforamance of a person in all the 3 tables.

So if I have to write this in excel it would go like this:

=(sumifs(table1score,table1name,name)+sumifs(table2score,table2name,name)+sumifs(table3score,table3name,name))/(sumifs(table2smaxscore,table1name,name)+sumifs(table2smaxscore,table2name,name)+sumifs((table3smaxscore,table3name,name))

How can I do the same in power BI?

Please help me with this as I've been looking this in the web but couldn't fidn anything at all.

I saw the earlier function but it doesn't work.

Regards,

Pradeep Singh

Re: SUMIFS in DAX

AlB — Thu, 05 Sep 2019 08:10:03 GMT

Hi Anonymous

Can you show a sample of the tables in your data model and an example with the expected result?

Please have a look at these tips for getting your question answered quickly.

Re: SUMIFS in DAX

Anonymous — Thu, 05 Sep 2019 08:30:12 GMT

Hi AIB,

My appologies, please find below the attachements of the tables.

The result that I want should be like this. As you see it's calculating score achieved by a person from all 3 tables and so as the max score. Rest I've devided score achieved by Max Score. What I'm not able to do is take out score and Max score via DAX.

Name	Score	Max Score	%
Pradeep Singh	5850	6600	89%
Debdip Shina	1710	2400	71%
Jason Martin	860	1200	72%
Steven Milene	6330	8700	73%
Justin Smith	1350	1400	96%
Nick Jonas	860	1200	72%
Sam Smith	800	800	100%

I hope you got what I'm trying to explain here.

Regards,

Pradeep Singh

Re: SUMIFS in DAX

AlB — Thu, 05 Sep 2019 10:29:27 GMT

Anonymous

Create a one-column table with all the names and create 1 to many relationships with the three tables. If you place the names (from the new table) in the rows of a matrix visual, you can create a measure:

Measure =
DIVIDE (
    SUM ( Table1[Score] ) + SUM ( Table2[Score] ) + SUM ( Table3[Score] ),
    SUM ( Table1[MaxScore] ) + SUM ( Table2[MaxScore] ) + SUM ( Table3[MaxScore] )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers

Re: SUMIFS in DAX

Anonymous — Thu, 05 Sep 2019 11:19:51 GMT

I did what you said but it isn't giving me the accurate data.

Please see the attached screenshot of the result. What it should give me is shown below.

Name	Score	Max Score	%
Pradeep Singh	5850	6600	88.6%
Debdip Shina	1710	2400	71.3%
Jason Martin	860	1200	71.7%
Steven Milene	6330	8700	72.8%
Justin Smith	1400	1400	100.0%
Nick Jonas	860	1200	71.7%
Sam Smith	800	800	100.0%

Thansk for helping me out with this.