DAX - Display the top 1 count in a calculated column

Question

I have a [Company] column, I have a [Billing day] column in a powerpivot sheet. A Company can have several different Billing days.

I want to show in a Pivot table, next to the company name, the Billing day with the highest number of occurences. The tricky part is that the number must appear in a [Calculated Column] that will not be put as a Measure in my Pivot table, but as a Column. I have reviewed such posts as: this one or this one. I have learned much but I am still unable to get the correct values.

Any idea ? Many thanks

Answer 1

This may not be the best way to get you what I understand you are asking for, but it is a way. I believe it achieves what you want regarding the calculated column.

If I start with this table as Table1:

Then I add a column using this code: MaxByDate = COUNTX(FILTER(FILTER(Table1,Table1[Company]=EARLIER(Table1[Company])),Table1[Billing Day]=EARLIER(Table1[Billing Day])),Table1[Billing Day])

And I add another column using this code: MaxOverall = MAXX(FILTER(Table1,Table1[Company]=EARLIER(Table1[Company])),COUNTX(FILTER(FILTER(Table1,Table1[Company]=EARLIER(Table1[Company])),Table1[Billing Day]=EARLIER(Table1[Billing Day])),Table1[Billing Day]))

Then I add one last column using this code: DateOfMaxOverall = FORMAT(SUMX(FILTER(FILTER(Table1,[Company]=EARLIER([Company])),[MaxByDate]=[MaxOverall]),[Billing Day])/COUNTX(FILTER(FILTER(Table1,[Company]=EARLIER([Company])),[MaxByDate]=[MaxOverall]),[Billing Day]),"m/d/yyyy")

I get this table:

Then if I choose to create a Flattened PivotTable from it (Home tab / PivotTable dropdown arrow button / Flattened PivotTable), and set things up like this:

...and turn off the subtotals and grand totals, I get this:

Which is, I believe, what you requested.

But I think a simpler approach might be this:

Start with the first table (Table1):

And, using that table as is, create a Flattened PivotTable directly. Then set the PivotTable up like this:

...and turn off the subtotals and grand totals, to get this:

...Which you can then filter for the max occurring day for each company:

...to get this: