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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jun 01, 2023

Fabio747 

In any case it's better to generate Date table and linked all your fact tables to it. When filter based on it.

From your screenshot it's not clear which relationships are for the tables. Perhaps you could share sample file instead of screenshot?

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 07, 2023

    Fabio747 

    Perhaps that could be

    FTE new:=SUMX( VALUES( Quarters[Q] ), [Total ORI_CS] / [Day in Months Total] / 😎

    if I understood the logic correctly.

    In above I used added explicit measures instead of implicit ones.

    • 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.

Resources