HC based on Quater

[unnijoy]

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.

Country	Qtr	Emp	HC
India	Qtr 1 2019	Sam	234
India	Qtr 1 2019	Raj	234
India	Qtr 1 2019	kit	234
India	Qtr 1 2019	mas	234
India	Qtr 1 2019	den	234
Canada	Qtr 1 2019	Dax	324
Canada	Qtr 1 2019	fix	324
Canada	Qtr 1 2019	nick	324
Canada	Qtr 1 2019	rick	324
India	Qtr 2 2019	Sam	456
India	Qtr 2 2019	Raj	456
India	Qtr 2 2019	kit	456
India	Qtr 2 2019	mas	456
India	Qtr 2 2019	den	456
Canada	Qtr 2 2019	Dax	653
Canada	Qtr 2 2019	fix	653
Canada	Qtr 2 2019	nick	653
Canada	Qtr 2 2019	rick	653

Qtr 1 2019		Qtr 2 2019
Country	HC		Country	HC
India	234		India	456
Canada	324		Canada	653

[v-xuding-msft]

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]

@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 · Accepted solution]

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.

[unnijoy]

hi @v-xuding-msft ,

When i update the bi with new data i can see that i have the latest data till up to Qtr 3 2020. I put the country in X axis and % as vlaues. And i keep QTR as filter. Q3 2020 data is only availabe for India. But when i select all the quarter as per the above formula it show show the graph with QTR 3 2020 data. But currently it is showin all the available country. I mean India with QTR 3 2020 data and the other country with QTR 2 2019. Hoe can i modify the formula so that when i select all QTR it show show the latest QTR data. In our case QTR 3 2020. The countries with no QTR 3 2020 data show not come.

[unnijoy]

@v-xuding-msft 

Thank you very much 🙂

[v-xuding-msft]

You're welcome.😉