Get Maximum of Value Field in Pivot Table

[AEAA]

Hi All,

I am trying to do the following, please let me know if this is possible:

I have a "Value Field" called Count of Value_Proficiency which simply counts the occurrence of each Row Label

I would like to create a new "Measure" having Max of Count of Value_Proficiency repeated for all Row Labels. Seems fairly simple, but I cannot get it to work (All the data is in a Data Model and I cannot create calculated fields I think).

I am trying the following -> Create Measure -> MAX([Count of Value_Proficiency]) but this does not work.

In example the max is 471, I would like to divide Sum of Value_Proficiency per row/471

Thank you very much for your help.

 

[Oaktree]

You can't do this with a native pivot table, but you *can* do this using DAX expressions within a PowerPivot based Pivot Table.

See Bill's video here for a similar example:

 

[AEAA]

Hello,

Thank you. I think I am getting close but still haven't been able to figure it out, sorry. I am trying to set up my DAX function as follows:

I create the measure CountTrial, to be able to refer to it in my next measure, and it works fine (see Count of Value_Proficiency2 matches CountTrial) (Maybe this step is unnecessary)

Then I try to get the max of this column (equals to 471) in every column, and I do the following:

And it returns 1 everywhere, as if its comparing every row and returning true for the max being in that row.

Would you know why this is not working?

 

[AEAA]

Hello,

Thank you. I think I am getting close but still haven't been able to figure it out, sorry. I am trying to set up my DAX function as follows:

View attachment 77921

I create the measure CountTrial, to be able to refer to it in my next measure, and it works fine (see Count of Value_Proficiency2 matches CountTrial) (Maybe this step is unnecessary)

View attachment 77922

Then I try to get the max of this column (equals to 471) in every column, and I do the following:

View attachment 77923

And it returns 1 everywhere, as if its comparing every row and returning true for the max being in that row.

Would you know why this is not working?

Just an update, Value_Proficiency is a list with 0, 0.33, 0.66 and 1 so the 1 might be coming from MAX(Value_Proficiency)

 

[AEAA]

Just an update, Value_Proficiency is a list with 0, 0.33, 0.66 and 1 so the 1 might be coming from MAX(Value_Proficiency)

Sorry for the spam, not sure if I can edit my original post.

Update:

I created a calculated column with the following syntax and I can achieve my original goal. However, when I apply a filter to the table the values do not get recalculated.

=MAXX(Proficiency;COUNTX(FILTER(Proficiency;EARLIER(Proficiency[Software])=Proficiency[Software]);Proficiency[Software]))

 

[Oaktree]

Try this:

=MAXX(CALCULATETABLE(VALUES(Proficiency[Software]),ALLSELECTED(Proficiency[Software])),[CountTrial])