Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago

HC calculation

Hi amitchandak 

 

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

 

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.

India456
Canada653

 

Need Help

8 Replies

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

    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's avatar
      unnijoy
      Post Prodigy
      6 years ago

      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's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        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