Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculate multiple columns in query editor

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

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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