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
Super User

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
Super User

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!