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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ManjunathaEP
Helper II
Helper II

Calculation using 2 data cards %

Hi, Thank you in advance for helping out for the below.

I have a results by year and I would like to calculate extrapolated rate for the current year using previous years average.

Data is as follows:

ManjunathaEP_0-1656511392298.png

I have a 2 data cards A is at 62% and B is at 57%, my objective is to calculate C (A/B = 92%). These are calculated using year filter. 

I tried doing C=divide(B,A), its giving the results of 100% as I have not filtered for any feilds. 

Please help me to arrive C,

 

2 ACCEPTED SOLUTIONS

Hi,

These measures work

Average win % from 2017 - 2021 = AVERAGEX(filter(summarize(VALUES('Table'[Year]),'Table'[Year],"ABCD",[Sum_Win],"EFGH",[Sum_Opps]),'Table'[Year]>=2017&&'Table'[Year]<=2021),DIVIDE([ABCD],[EFGH]))
Win % in 2022 = AVERAGEX(filter(summarize(VALUES('Table'[Year]),'Table'[Year],"ABCD",[Sum_Win],"EFGH",[Sum_Opps]),'Table'[Year]=2022),DIVIDE([ABCD],[EFGH]))
Measure = DIVIDE([Win % in 2022],[Average win % from 2017 - 2021])

Hope this helps.

Untitled.png


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

View solution in original post

I have helped you enough on this.  I rewrote all formulas in the file which i shared with you today.  Looks like tyou have not even looked at that file.  Someone else will help you


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

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of your PBI file.  Ensure that you already have your measures for A and B there.


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

Hi,

These measures work

Average win % from 2017 - 2021 = AVERAGEX(filter(summarize(VALUES('Table'[Year]),'Table'[Year],"ABCD",[Sum_Win],"EFGH",[Sum_Opps]),'Table'[Year]>=2017&&'Table'[Year]<=2021),DIVIDE([ABCD],[EFGH]))
Win % in 2022 = AVERAGEX(filter(summarize(VALUES('Table'[Year]),'Table'[Year],"ABCD",[Sum_Win],"EFGH",[Sum_Opps]),'Table'[Year]=2022),DIVIDE([ABCD],[EFGH]))
Measure = DIVIDE([Win % in 2022],[Average win % from 2017 - 2021])

Hope this helps.

Untitled.png


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

Dear Ashish, Thank you for the previous measures and it is useful. Now, again thank you in advance for helping me out with another measure.

With the same example below, I would like to get the extrapolated rate i.e., 

Win % in 2022(57.01%)*Measure (91.95%) = 52.42%

You are welcome.

Just multiply the 2 measures.


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

Hi, thank you for your reply. I tried multiplying the 2 measures but it is not giving the right results.

I think this is not right because of 2 different quarter selections.

Win % in 2022 = AVERAGEX(filter(summarize(VALUES('Table'[Year]),'Table'[Year],"ABCD",[Sum_Win],"EFGH",[Sum_Opps]),'Table'[Year]=2022),DIVIDE([ABCD],[EFGH]))
Measure = DIVIDE([Win % in 2022],[Average win % from 2017 - 2021])

 Now, I have to multiply Measure*Win % in 2022.. this is not giving the right calculations

Request you to have a look and help me.

.pbix file link is here https://drive.google.com/file/d/1UqlJiogfwjl_zTesUstYGOK2cgcGX0xY/view?usp=sharing

I get the correct answer - see Measure 2.  Download the PBI file from here.

Untitled.png


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

Thank you, Ashish. Thank you so much for your continuous help on this. Actually, my requirement is to calculate win rates and extrapolated rates for each quarter and check for the current year's quarter. I am able to do all the calculations with your support other than the multiplication of current year quarter win rate with the extrapolated win rate of the previous quarter. Requirement to calculate D*C = 68.9%

A.

ManjunathaEP_0-1660840151570.png

B. 

ManjunathaEP_1-1660840236375.png

 

C. 

ManjunathaEP_2-1660840267709.png

 

D.

ManjunathaEP_3-1660840296767.png

 

I still do not know what you want .  Download the revised file from here.

Hope this helps.

Untitled.png


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

Sorry for the confusion.

Please find the .pbix file (Ref Table (2)) and request you to help. Thank you as always.

https://drive.google.com/file/d/1F5z4vESS1uLquMzqi6Pr591lu3aQYjag/view?usp=sharing

I have helped you enough on this.  I rewrote all formulas in the file which i shared with you today.  Looks like tyou have not even looked at that file.  Someone else will help you


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

Kindly support on the above query. I am thankful in advance for the support

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.