Calculate multiple columns in query editor

[Anonymous]

Hello Community,

I have a requirement to calculate multiple values and get a total with an objective of them calculating a percentage.

I'm currently handling the below with Card Visual filters to get the required values, but I need to also do it in the query editor becuase I don't seem to be able to get a percentage based on two card values unfortunately.

Here is some sample data

I need to be able to calculate all Level 1-3's for each of the sections and get a vaue for Open and Closed items

Section 1 + Count of all Level 1s + Status = Open

Section 1 + Count of all Level 1s + Status = Closed
Section 1 + Count of all Level 1s + Status = Open + IsOverdue = Yes

Audit Section	Recommendation	IsOverdue	Status
Section 1	Level 2	Yes	Open
Section 1	Level 3	No	Closed
Section 2	Level 1	Yes	Open
Section 2	Level 3	No	Closed
Section 3	Level 1	Yes	Open
Section 3	Level 2	No	Closed

I think I need a number of custom columns like below to then get my totals for working out the percentage of overdue items. 

Section 1 Level 1 Open Count	Section 1 Level 1 Closed Count	Section 1 Level 1 Overdue Count
Value	Value	Value

I hope this is clear. Any help would be greatly appreciated!

Thanks, M

[v-juanli-msft]

Hi @Anonymous

It is not clear about your requirement.

1. Card Visual filters

Does it refer to a card visual?

2.get a percentage based on two card values

which columns do you add in two card visual, how does the percentage calculate?

3.

Section 1 + Count of all Level 1s + Status = Open

Section 1 + Count of all Level 1s + Status = Closed
Section 1 + Count of all Level 1s + Status = Open + IsOverdue = Yes  ???

I don't understand, does it mean:

Section 1 Level 1 Open Count=Count of all Level 1 (which are belong to Section 1 and its Status is open)

4.Section 1 Level 1 Overdue Count

How to calculate this?

From your information, i believe we could use DAX formula or power query to achieve the goal, please let me know more clear.

Best Regards

Maggie