cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vnaga85
Frequent Visitor

Dynamically Calculate Sum based on the filter

Hi

I have table like below                                                                                  

Product

Rewardamount

Bill amount

Month

A

100

60

1

A

40

70

2

A

30

10

3

A

50

70

4

B

50

60

1

B

20

70

2

B

200

10

3

B

50

70

4

C

100

60

1

C

60

70

2

C

30

10

3

C

100

70

4

 

And I have calendar filter.

Here I need to find the count of eligible product based on the following condition for selected month

Condition is Rewardamount > Billamount then the production eligible.

 

For example if choose month 1 and 2 then it has to calculated something like this

Product A = (100 + 40) > (60 + 70)

Product B = (50+20) > (70 + 60)

Product C = (100+60) > (60 + 70)

The result should be 2, because product B is not eligible as per the condition. I need to calculate in this way dynamically.

 

Please someone help me on this.

 

Thanks in Advance

Nagarajan. V

 

2 ACCEPTED SOLUTIONS
mehaboob557
Resolver IV
Resolver IV

Hi @vnaga85,

 

You have to create Measures to achieve this.

 

I am sharing a pbix file here.

 

You will find the measure in that. Please check.

 

If you feel my answer is helped you. Please give me one Like(kudo) to my answer and accept as solution.

 

Hope this help you Nagarajan.

 

Regards,

Mehbub - India

View solution in original post

Ashish_Mathur
Super User III
Super User III

Hi,

 

This formula will work

 

=COUNTROWS(FILTER(SUMMARIZE(Data,Data[Product],"ABCD",SUM(Data[Rewardamount]),"EFGH",SUM(Data[Bill amount])),[ABCD]>[EFGH]))

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User III
Super User III

Hi,

 

This formula will work

 

=COUNTROWS(FILTER(SUMMARIZE(Data,Data[Product],"ABCD",SUM(Data[Rewardamount]),"EFGH",SUM(Data[Bill amount])),[ABCD]>[EFGH]))

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

This is perfect.  

 

Thanks a lot

Nagarajan. V

mehaboob557
Resolver IV
Resolver IV

Hi @vnaga85,

 

You have to create Measures to achieve this.

 

I am sharing a pbix file here.

 

You will find the measure in that. Please check.

 

If you feel my answer is helped you. Please give me one Like(kudo) to my answer and accept as solution.

 

Hope this help you Nagarajan.

 

Regards,

Mehbub - India

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors