Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.