Forum Discussion

ManjunathaEP's avatar
ManjunathaEP
Helper II
4 years ago
Solved

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 follow...
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

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.

  • ManjunathaEP's avatar
    ManjunathaEP
    Helper II
    4 years ago

    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%

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      You are welcome.

      Just multiply the 2 measures.

      • ManjunathaEP's avatar
        ManjunathaEP
        Helper II
        4 years ago

        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