Forum Discussion
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 need to calculate the FTEs
I need a pivot where:
1) be able to filter by the months that are in table "Data"--> and for this I put the "months" as a filter-OK
2) view the values of the working days present in table Quarter, but only for the months for which I have filtered.
I have used power pivot and I have added the table to data model, but in the pivot, when I change the month filter, I still see the value of working days for all months not just for those filtered.
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.
7 Replies
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?
Hi SergeiBaklan thanks a lot for feedback!!I attach the xlsx file, could you help me understand how to set the pivot?
Thanls in advance!
Fabio
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.
