cancel
Showing results for
Did you mean:
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.

Nagarajan. V

2 ACCEPTED SOLUTIONS
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.

Regards,

Mehbub - India

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]))

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
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]))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

This is perfect.

Thanks a lot

Nagarajan. V

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.

Regards,

Mehbub - India

Announcements

#### 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.

#### Microsoft named a Leader in The Forrester Wave

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

#### 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 Solution Authors
Top Kudoed Authors