HC calculation

[unnijoy]

Hi @amitchandak 

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

Above is my data base .

I want a formula which will give me the HC like if am selecting Qtr 1 2019 then 

India HC= 234

Canada HC = 324

And if am selecting both Qtr 1 2019 and Qtr 2 2019 then the HC sholuld be the latest HC that is Qtr 2 2019.

India

456

Canada

653

[v-gizhi-msft]

Hi,

Please try to create a seperate slicer table first:

Slicer Table = DISTINCT(SELECTCOLUMNS('Table',"Qtr",'Table'[Qtr]))

Then try this measure:

Measure = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( 'Slicer Table'[Qtr] ) = "Qtr 1 2019", IF ( MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1, 0 ),
    SELECTEDVALUE ( 'Slicer Table'[Qtr] ) = "Qtr 2 2019", IF ( MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1, 0 ),
    DISTINCTCOUNT ( 'Slicer Table'[Qtr] )
        = CALCULATE ( COUNTROWS ( 'Slicer Table' ), ALL ( 'Slicer Table' ) ), IF (
        MAX ( 'Table'[Quarter] ) = MAXX ( ALL ( 'Table' ), 'Table'[Quarter] ),
        1,
        0
    )
)

Then apply it to the table visual by setting measure=1, when select one value in slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[unnijoy]

@v-gizhi-msft ,

Thanks for the quick reply.

Here we have fixed it to only 2 quarter. Qtr 1 2019 and Qtr 2 2019.

But our data will keep on increasing. So other quarters will also come. So how can we make this dynamic. Like if we have quarter data from 2019 till date and if we select all it should show latest quarter HC or if we are selecting only a particulr quarter then it shoud show that quarter HC

Please help.

[v-gizhi-msft]

Hi,

Please try to create two columns first:

Year = RIGHT(RIGHT('Table'[Qtr],6),4)
Quarter = LEFT(RIGHT('Table'[Qtr],6),1)

Then try this measure:

Measure = 
SWITCH (
    TRUE,
    MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1,
    DISTINCTCOUNT ( 'Slicer Table'[Qtr] )
        = CALCULATE ( COUNTROWS ( 'Slicer Table' ), ALL ( 'Slicer Table' ) ), IF (
        MAX ( 'Table'[Quarter] ) = MAXX ( ALL ( 'Table' ), 'Table'[Quarter] )
            && MAX ( 'Table'[Year] ) = MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
        1,
        0
    )
)

And this measure can adapt any new data by Qtr.

Hope this helps.

Best Regards,

Giotto

[unnijoy]

@v-gizhi-msft  thank you very much.

Can you add this in the powerbi file and share it with me. so it will be very helpfull for me.

Waiting for your reply

[v-gizhi-msft]

Hi,

I am sorry to have kept you waiting, here is my test pbix file:

pbix 

If my answer has solved your issue, please mark it as a solution for others to see.
Thanks!

Best Regards,

Giotto

[unnijoy]

@v-gizhi-msft  I am not able to use this to calculate the %.

We are caculating the award % using the below Formula.

Awardf %= total award/ HC for that quarter,

But when i use the current formula i am getting wrong figure.

Please help.

[v-gizhi-msft]

Hi,

For your requirement, i add some new data to original table:

Then try this measure:

Total Award/HC = SUM('Table'[Award])/MAX('Table'[HC])

It works well here:

If you want to contract the HC value, just try this:

_HC = CALCULATE(MAX('Table'[HC]),FILTER('Table',[Measure]=1))

Hope this helps.

Best Regards,

Giotto

[unnijoy]

Hi @v-gizhi-msft ,

thanks for the reply.

Total award is counted by the count of employees.

 so in the below table if we gave employee id it will make our calculation easy.

So total award = count of employee id

I tried the formula that you gave. the issue is that the vale is not changing. it is always showing the latest month HC

can you please show it in a powerbi file and share it with me

Can you use the formul in graph as below

X axis = Country name

Value = count of employee ID/ HC

Note: HC = HC of the selected quarter. IF all quarter is selectd then it should take Latest month HC.

Please share the powerbi file also.

I am using link and stacked column chart in powerbi.

Thanks and sorry for asking all this modification. Today is my project deadline data. Thank you: