SUMX formula to iterate if specific conditions are met?

[Anonymous202020]

Hello, just recently started using Power BI and I'm stuck on how I can use it to extract information

Date	ID	Title	Gender	First Name	Last Name	Qual	Grade	Description	GradeValue
16-Mar-2021	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2022	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2023	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2024	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2025	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2026	12345	Mr	Male	John	Doe	B	F	Fail	0

I have my table setup similar to this. I need to be able to write a formula that looks at the ID and the Qual, if they are the same then add up the Grade Value. And if the total grade value is 2 then it's a pass, anything less then it's a fail. Also I need to count it as 1 register for each Qual. Any help would be much appreciated, thank you!

[Anonymous · Accepted solution]

Thanks for the reply from HotChilli , please allow me to provide another insight: 
Hi  @Anonymous202020 ,

Here are the steps you can follow：

1. Create calculated column.

Test1 =
SUMX(
    FILTER(ALL('Table'),
    'Table'[ID]=EARLIER('Table'
[ID])&&'Table'[Qual]=EARLIER('Table'[Qual])),[GradeValue])

Test2 =
var _maxpdate=
MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Qual]=EARLIER('Table'[Qual])&&[Grade]="P"),[Date])
var _maxnodate=
MAXX(FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])&&'Table'[Qual]=EARLIER('Table'[Qual])),[Date])
return
IF(
    [Test1]=2 && [Date]=_maxpdate,1,
IF(
    [Test1]<2&&[Date]=_maxnodate,1,0))

2. Create calculated table.

Table 2 =
var _table=
FILTER(
    'Table',[Test2]=1)
return
SUMMARIZE(
    _table,
    [ID],[Title],[Gender],[First Name],[Last Name],[Qual],
    "Grade",MAXX('Table',[Description]),
    "Grade Value",MAXX('Table',[Test1]))

3. Result:

Best Regards,

Liu Yang

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

[HotChilli]

What I'm saying is the Grade column in the expected results looks like the original 'description' column, it doesn't look like a new measure which looks at the 'Grade Value' column in the visual and assigns pass or fail.

My evidence is the last line which shows Grade Value 1 and 'pass'

[Anonymous202020]

Ah yes I get what you mean

Date	ID	Title	Gender	First Name	Last Name	Qual	Grade	Description	GradeValue
16-Mar-2021	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2022	12345	Mr	Male	John	Doe	A	P	Merit Pass	1
16-Mar-2023	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2024	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2025	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2026	12345	Mr	Male	John	Doe	B	F	Fail	0
16-Mar-2021	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2022	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2023	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2024	54321	Mr	Male	Phil	Reed	A	P	Distinction	1
16-Mar-2025	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2025	54321	Mr	Male	Phil	Reed	B	P	Pass	1

Sorry I forgot to include the different types of passes you can get.

[HotChilli]

Thanks for the data - that is better than 99% of the posts on the forum (sorry, other posters).

So you want a table visual with the first 6 fields from the data table. Grade Value looks like an ordinary SUM aggregation on the field.

I don't understand the 'grade' column. Is it the Description column from the table? In which case, fine I think the expected output is achieved. However, I don't think the requirement:

" if the total grade value is 2 then it's a pass, anything less then it's a fail"  has been addressed.

Thoughts?

[Anonymous202020]

I was thinking I could have the Grade column read the value of the Grade Value in the new table and then give a Pass or Fail.

[HotChilli]

Calculated column(s) or measure(s)?

If it's a measure, please show the expected output.

--

I think the test data should be expanded to include a) another ID b) some data that adds up to 2 otherwise there's no 'pass'

[Anonymous202020]

I have expanded the table

Date	ID	Title	Gender	First Name	Last Name	Qual	Grade	Description	GradeValue
16-Mar-2021	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2022	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2023	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2024	12345	Mr	Male	John	Doe	A	P	Pass	1
16-Mar-2025	12345	Mr	Male	John	Doe	A	F	Fail	0
16-Mar-2026	12345	Mr	Male	John	Doe	B	F	Fail	0
16-Mar-2021	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2022	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2023	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2024	54321	Mr	Male	Phil	Reed	A	P	Pass	1
16-Mar-2025	54321	Mr	Male	Phil	Reed	A	F	Fail	0
16-Mar-2025	54321	Mr	Male	Phil	Reed	B	P	Pass	1

The expected output would be something like this 

ID	Title	Gender	First Name	Last Name	Qual	Grade	Grade Value
12345	MR	Male	John	Doe	A	Pass	2
54321	MR	Male	Phil	Reed	A	Fail	1
12345	MR	Male	John	Doe	B	Fail	0
54321	MR	Male	Phile	Reed	B	Pass	1