Forum Discussion
HC calculation
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
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
Hi,
I am sorry to have kept you waiting, here is my test pbix file:
If my answer has solved your issue, please mark it as a solution for others to see.
Thanks!Best Regards,
Giotto
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.
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