Forum Discussion

ABC11's avatar
ABC11
Icon for Resolver I rankResolver I
2 years ago
Solved

convert measure to column

Hello,

I created measure but total and grand total are incorrect. Please help me to convert to column

Total Hrs =
CALCULATE(
    SUM(Query1[LABORHRS]),
    REMOVEFILTERS(Query1[AREA_NEW]))

Thanks

 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ABC11 ,

     

    Thanks for the reply from HotChilli .

     

    Calculated columns are calculated row-by-row and Measures are calculated dynamically based on the filter context.

     

    This is sample data that I created myself:

    Area

    User

    laborhrs

    A

    Jack

    5

    B

    Jack

    5

    A

    Rose

    3

    B

    Rose

    3

    C

    Rose

    4

    A

    Dragon

    5

    B

    Dragon

    5

    A

    Azik

    5

    B

    Azik

    5

    A

    Libra

    10

    A

    Clara

    5

    B

    Clara

    5

     

    No need to create calculated columns, your requirement is to get the number of employees by region.

     

    Create a measure:

     

    measure = 
SUMX(
    'Table',
    DIVIDE(
        'Table'[laborhrs],
        CALCULATE(
            SUM( 'Table'[laborhrs]),
            REMOVEFILTERS('Table'[Area]),
            ALLEXCEPT('Table', 'Table'[User])
        )
    )
)

     

     

    The page effect is as follows:

     

    The pbix file is attached.

     

    I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

3 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    It helps a bit if you show us some sample data and the desired result (plus an explanation of what's going on)

  • ABC11's avatar
    ABC11
    Icon for Resolver I rankResolver I
    2 years ago

    Hello,

    I am trying to get Headcount by area. AS example one employee work 10hrs a day in two different (5hrs each) area then each area should show 0.5 as headcount. below measure show me total hrs for a day.

    I have column laborhrs that show hrs by area.  if below measure can be convert to column then I can divide 

    DIVIDE(Query1[LABORHRS],Query1[Total HRS]) but currently I have in measure so I can't get divide and not getting total and grandtotal

    Total Hrs =
    CALCULATE(
        SUM(Query1[LABORHRS]),
        REMOVEFILTERS(Query1[AREA_NEW]))
    Thanks
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ABC11 ,

     

    Thanks for the reply from HotChilli .

     

    Calculated columns are calculated row-by-row and Measures are calculated dynamically based on the filter context.

     

    This is sample data that I created myself:

    Area

    User

    laborhrs

    A

    Jack

    5

    B

    Jack

    5

    A

    Rose

    3

    B

    Rose

    3

    C

    Rose

    4

    A

    Dragon

    5

    B

    Dragon

    5

    A

    Azik

    5

    B

    Azik

    5

    A

    Libra

    10

    A

    Clara

    5

    B

    Clara

    5

     

    No need to create calculated columns, your requirement is to get the number of employees by region.

     

    Create a measure:

     

    measure = 
SUMX(
    'Table',
    DIVIDE(
        'Table'[laborhrs],
        CALCULATE(
            SUM( 'Table'[laborhrs]),
            REMOVEFILTERS('Table'[Area]),
            ALLEXCEPT('Table', 'Table'[User])
        )
    )
)

     

     

    The page effect is as follows:

     

    The pbix file is attached.

     

    I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!