How to calculate maximum month for each quarter and for year from the available data set

[SAMJB]

How to calculate maximum month for each quarter and for year from the available data set

I have Fiscal year, Month, Quarter, Headcount columns, I want to display max month of headcount in every  quarter and  year in table chart.

Can someone help me on this.

[MayilVahanan]

HI

Try like below

For Quarter: Sum({<Gender = {'M'}>}Sales) / Sum(Sales))

For Max month of Quarter: Sum(Aggr(If(Month = Max(Total<Quarter, Year>Month), Sum({<Gender = {'M'}>}Sales) / Sum(Sales)), Quarter, Month, Year))

If not works, pls attach the sample file with expected result

[SAMJB]

Hi Mayil Vahanan,

Thanks for your response.

Below expression i have modified.

Sum(Aggr(If([Fiscal Month] = Max(Total<FiscalQuarter, [Fiscal Year]>[Fiscal Month]),
Count({<Gender = {'M'}>}[PS No]) / Count (distinct [PS No])), FiscalQuarter, [Fiscal Month], [Fiscal Year]))

total percentage is above 100

[SAMJB]

In Pivot table i want to display headcount and gender wise data only max month of every Quarter for all year

below one is full data in table for selected year

Below sample data in pivot table

below expression I'm using for gender

Count({<Gender={'M'}>}distinct [PS No])/Count(distinct [PS No])

[MayilVahanan]

Hi

In that case, u need to use Avg

Avg(Aggr(If([Fiscal Month] = Max(Total<FiscalQuarter, [Fiscal Year]>[Fiscal Month]),
Count({<Gender = {'M'}>}[PS No]) / Count (distinct [PS No])), FiscalQuarter, [Fiscal Month], [Fiscal Year]))

[SAMJB]

Hi MayilVahanan,

Thanks for the Expression, It's working fine.

Avg(Aggr(If([Fiscal Month] = Max(Total<FiscalQuarter, [Fiscal Year]>[Fiscal Month]),
Count({<Gender = {'M'}>}[PS No]) / Count (distinct [PS No])), FiscalQuarter, [Fiscal Month], [Fiscal Year]))

I have one more doubt.

With same expression i wont to show max month headcount of the year means Mar month.