Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago
Solved

HC based on Quater

Hi I have a data base with country name, quarter , employee name and HC.

 

Every quarter we will update the HC. Below is the table. I need a formula that will calculate the HC based on the quarter that we select. IF all quarter is selected then it should show the HC based on the latest quarter.

CountryQtrEmpHC
IndiaQtr 1 2019Sam234
IndiaQtr 1 2019Raj234
IndiaQtr 1 2019kit234
IndiaQtr 1 2019mas234
IndiaQtr 1 2019den234
CanadaQtr 1 2019Dax324
CanadaQtr 1 2019fix324
CanadaQtr 1 2019nick324
CanadaQtr 1 2019rick324
IndiaQtr 2 2019Sam456
IndiaQtr 2 2019Raj456
IndiaQtr 2 2019kit456
IndiaQtr 2 2019mas456
IndiaQtr 2 2019den456
CanadaQtr 2 2019Dax653
CanadaQtr 2 2019fix653
CanadaQtr 2 2019nick653
CanadaQtr 2 2019rick653

 

Qtr 1 2019 Qtr 2 2019
CountryHC CountryHC
India234 India456
Canada324 Canada653
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi unnijoy ,

    I modified the formulas. Please try and check if it is what you want?

    HC =
VAR vHC =
    CALCULATE (
        MAX ( 'Table'[HC] ),
        FILTER ( 'Table', 'Table'[Qtr] = SELECTEDVALUE ( 'Slicer Table'[Qtr] ) )
    )
RETURN
    IF ( HASONEFILTER ( 'Slicer Table'[Qtr] ), vHC, MAX ( 'Table'[HC] ) )


Count of Awardees =
CALCULATE (
    COUNT ( 'Table'[Emp] ),
    ALLEXCEPT ( 'Table', 'Table'[Country], 'Table'[Qtr] )
)


Percentage = DIVIDE([Count of Awardees],[HC])

    For more details, please see the attachment.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    6 years ago

    Hi unnijoy ,

    Please have a try like this :

    • Create a new table 
    Slicer Table = VALUES('Table'[Qtr])
    • Create a measure
    Measure =
VAR slicer =
    SELECTEDVALUE ( 'Slicer Table'[Qtr] )
VAR vMaxQtr =
    MAXX ( ALL ( 'Slicer Table' ), MAX ( 'Slicer Table'[Qtr] ) )
VAR vHC =
    CALCULATE ( SUM ( 'Table'[HC] ), FILTER ( 'Table', 'Table'[Qtr] = slicer ) )
VAR vLatestHC =
    SUMX ( FILTER ( 'Table', 'Table'[Qtr] = vMaxQtr ), SUM ( 'Table'[HC] ) )
RETURN
    IF ( HASONEFILTER ( 'Slicer Table'[Qtr] ), vHC, vLatestHC )

    For more details, please see the attachement.

     

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      6 years ago

      v-xuding-msft  thanks for the help. Can we change the formula in such a way thta i can get the HC like Quarter 2 2019  India = 456.

       

      Why because am i the list of people how got award so i have to divide the count of awardees / HC of the country for a particulr month. 

      For example Qtr  2 2019 India totla number of awardees is 200

      We are calculating the award % by total number of awardees/ HC of that purticular quarter. so in this case it will be

       

      200/456=43.85%.

       

      If are selecting all quarter then the HC should be for the latest quarter.

      Please help

       

      • v-xuding-msft's avatar
        v-xuding-msft
        Community Support
        6 years ago

        Hi unnijoy ,

        I modified the formulas. Please try and check if it is what you want?

        HC =
VAR vHC =
    CALCULATE (
        MAX ( 'Table'[HC] ),
        FILTER ( 'Table', 'Table'[Qtr] = SELECTEDVALUE ( 'Slicer Table'[Qtr] ) )
    )
RETURN
    IF ( HASONEFILTER ( 'Slicer Table'[Qtr] ), vHC, MAX ( 'Table'[HC] ) )


Count of Awardees =
CALCULATE (
    COUNT ( 'Table'[Emp] ),
    ALLEXCEPT ( 'Table', 'Table'[Country], 'Table'[Qtr] )
)


Percentage = DIVIDE([Count of Awardees],[HC])

        For more details, please see the attachment.

         

        Best Regards,

        Xue Ding

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.