Forum Discussion

Fabio747's avatar
Fabio747
Copper Contributor
Jun 01, 2023
Solved

Pivot data filtered by another table column

Hello, I have two tables: 1) table "Data" with the hours provided per month 2) table "Quarter" with all the months of the year in wich for each month there is the number of working days that I nee...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 09, 2023

    Fabio747 

    Perhaps as

    FTE-CS :=
SUMX (
    VALUES ( Quarters[Q] ),
    [ORE_CS per Hour] / [Somma di NUM_GG_STD]
)

    where

    ORE_CS per Hour :=
SUMX (
    FactData,
    FactData[ORE_CS] / FactData[DayWorkHours]
)

     and I added DaysWorkHours calculated columns to convert texts in HoursDays into numbers. If in HoursDays will be numbers that's not necessary.

Fabio747's avatar
Fabio747
Copper Contributor
Jun 09, 2023

SergeiBaklan 

 

THANKS!!!!! it works fine!!

 

Now I have another issue: the formula calculates FTEs based on 8 hours per day. However, the number of hours/day to calculate the FTE is not always 8, but it changes according to the team.
To do this, I'he added the HourDays column in the FactData table populated with the hour/day of related team and I thought of using that in the formula you suggested, but it doesn't work. Is there any way to fix?

I attach the xls file

Thanks in advance for suggestions

Fabio

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 09, 2023

Fabio747 

Perhaps as

FTE-CS :=
SUMX (
    VALUES ( Quarters[Q] ),
    [ORE_CS per Hour] / [Somma di NUM_GG_STD]
)

where

ORE_CS per Hour :=
SUMX (
    FactData,
    FactData[ORE_CS] / FactData[DayWorkHours]
)

 and I added DaysWorkHours calculated columns to convert texts in HoursDays into numbers. If in HoursDays will be numbers that's not necessary.

  • Fabio747's avatar
    Fabio747
    Copper Contributor
    Jun 13, 2023

    SergeiBaklan it works fine!!

    Thanks!!

     

    Fabio

Resources