Hello,
I have a table 'Main' with column
State
City
Store
Date
Month: Aug 2022, Sep 2022, Oct 2022.. Aug 2023, 3 Month, 6 Months, and 12 Months
-> Because my user would like to see the average 3 months ,6 month, and 12 month at the column level. So, I create a
dataset wih full 13 month - union 3 months, union 6 months, and union 12 months.
Month_Sort: to sort the Month column: If Month = Aug 2022 then 1, Sep 2022 then 2... 3 Months then 20, 6 Months then 21,
and 12 Months then 22
Flag: If Column A = "Emplyee" then "Employee", else "Not Emplyee"
Full Time: If Flag = Employee and Type = "Yes" then "Full Time" else "NULL"
I created 3 measures"
[Employee] = CALCULATE ( SUM ( 'Main'[Count]) , 'Main'[Flag] = "Emplyee")
[Full Time] = CALCULATE ( SUM ( 'Main'[Count]) , 'Main'[Full Time] = "Full Time")
[% of Full Time] = [Full Time] / [Employee]
I created a matrix with Month as Column; State, City, and Store as rows (drill down) and [% of Full Time] as measure.
For [% of Full Time], i would like to show something like:
If Month in {"Aug 2022", "Sep 2023"....} then [% of Full Time]
If Month = "3 Monhs" then everage [% of Full Time] of last 3 month, exclude current motnh
If Month = "6 Monhs" then everage [% of Full Time] of last 6 month, exclude current motnh
If Month = "12 Monhs" then everage [% of Full Time] of last 12 month, exclude current motnh
The [% of Full Time] give me Sum of Full Time where Month = 3/6/12 Months / Sum of Employee where Month = 3/6/12 Months. This number is not correct. I would like to have sum of [% of Full Time] / number of month that have %.
Thank you so much for your time.
