cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Condition based formula

suppose I have data as illustrated in the picture link below. Does anyone know how I can embed the following formula 100(sum(KPI1)/sum(KPI1_COUNT))* as a new column. With the sum being based on each unique "Applicant_Company" and each month.

1 ACCEPTED SOLUTION
Super User

@Anonymous , earlier will kind of create a partition

This means a new column all Applicant_Company will same value if you replace it WORK_AREA, All WORK_AREA will have same will value .

Measn combination of [WORK_AREA] and Applicant_Company will have same value

divide(Sumx(filter(Table, [Applicant_Company] =earlier([Applicant_Company]) && [WORK_AREA] =earlier([WORK_AREA]) ),[KPI1]),Sumx(filter(Table, [Applicant_Company] =earlier([Applicant_Company]) && [WORK_AREA] =earlier([WORK_AREA])  ),[KPI1_COUNT])) *100

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!
3 REPLIES 3
Super User

@Anonymous , A new column like

divide(Sumx(filter(Table, [Applicant_Company] =earlier([Applicant_Company])),[KPI1]),Sumx(filter(Table, [Applicant_Company] =earlier([Applicant_Company])),[KPI1_COUNT])) *100

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!
Anonymous
Not applicable

Thanks! Can I ask, would the formula you produced also work if I wanted the sum to be based on 'WORK_AREA' as well as 'APPLICANT_COMPANY'?

Super User

@Anonymous , earlier will kind of create a partition

This means a new column all Applicant_Company will same value if you replace it WORK_AREA, All WORK_AREA will have same will value .

Measn combination of [WORK_AREA] and Applicant_Company will have same value

divide(Sumx(filter(Table, [Applicant_Company] =earlier([Applicant_Company]) && [WORK_AREA] =earlier([WORK_AREA]) ),[KPI1]),Sumx(filter(Table, [Applicant_Company] =earlier([Applicant_Company]) && [WORK_AREA] =earlier([WORK_AREA])  ),[KPI1_COUNT])) *100

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || Learn 50+ Power Query List Functions
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Announcements

#### CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors