How COUNT number MAX Values in column?

[citizenbh]

Sample:
I have count number max values by Type

Table:
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Type[/TD]
[TD="width: 64"]Value[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]67[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]178[/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]198[/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]256[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]248[/TD]
[/TR]
[TR]
[TD]o[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]180[/TD]
[/TR]
</tbody>[/TABLE]
________________________
Result:
[TABLE="width: 144"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Count(Max)[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
---------------------------
Thx

 

[KHorseman]

Table[CountMax]
:=CALCULATE(
    COUNTROWS(Table),
    FILTER(
        Table,
        Table[Value] = MAX(Table[Value])
    )
)

 

[Ozeroth]

An alternative using LASTNONBLANK:

=CALCULATE(
    COUNTROWS(Table),
    LASTNONBLANK( Table[Value], 0)
)

 

[KHorseman]

Could you explain how that use of LASTNONBLANK works? I'm having trouble wrapping my head around it. Does LASTNONBLANK treat the given column as being in ascending numeric order automatically? I don't see anything about that in the documentation but otherwise I don't get how it would give you the max. I also don't get the use of 0 as the expression. But this looks like a pretty cool trick if I could see what makes it work.

 

[Ozeroth]

Sure,

• LASTNONBLANK will return the "last" value of the single-column table (or column) provided in the first argument, restricted to values where the second argument is nonblank.
• Last = max for numbers, or lexicographically last for strings etc, while MAX only works with numbers.
• The other difference to MAX is that LASTNONBLANK actually returns a single-row, single-column table containing the "last" value (This will be treated as a scalar or table depending on context).
  This allows LASTNONBLANK to be used as a filter argument in CALCULATE, for example.
• By using an arbitrary nonblank second argument like I did (e.g. 0 or 1), LASTNONBLANK is just like MAX but returns a table rather than a scalar.

SQLBI has a good page on FIRST/LASTNONBLANK:
https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/

 

[citizenbh]

Thanks guys!

I've got such a table

Type

<tbody>
[TD="width: 59"]Value[/TD]
[TD="width: 164"]Sum of Table[CountMax][/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl63"]100[/TD]
[TD="align: right"]2[/TD]

[TD="class: xl63"]88[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]67[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]35[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl63"]200[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl63"]178[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]123[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl63"]300[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]256[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]248[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]198[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]180[/TD]
[TD="align: right"]1[/TD]

[TD="class: xl63"]Grand Total[/TD]

[TD="align: right"]15[/TD]

</tbody>

Is it possible to get a summary table above in the first post

 

[citizenbh]

Count the number different Values per group Type
=CALCULATE(DISTINCTCOUNT([Value]);FILTER(Table1;[Type]=EARLIER(Table1[Type])))

Account MAX Value per group Type
=CALCULATE(MAX([Value]);FILTER(Table1;[Type]=EARLIER(Table1[Type])))

When we put them in an area ΣValues all give the same result (ValueField Settings: Count) as previus posts,
but I can't get a summary table of the first post.

 

[Ozeroth]

You can get the summary table from the first post by using one of the Countmax measures (from posts 2-3) as long as you have Type but not Value as row fields.
Is that what you were getting at?

 

[citizenbh]

Exactly. Sorry, my mistake, the table should look like this:

Result:

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Type[/TD]
[TD="width: 64"]MAXVAL[/TD]
[TD="width: 64"]Count(Max)[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
---------------------------

 

[citizenbh]

Ok, I solved

[TABLE="width: 246"]
<tbody>[TR]
[TD]Type[/TD]
[TD]MAXVAL[/TD]
[TD]Table[CountMax][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Rows:
ΣValues:		Table[CountMax] - no calculation
DAX Column:		MAXVAL	=CALCULATE(MAX([Value]);FILTER('Table';[Type]=EARLIER('Table'[Type])))
		Table[CountMax]	- from post 2

<tbody>
[TD="width: 116"][/TD]
[TD="width: 238"]Type, MAXVAL[/TD]
[TD="width: 625"][/TD]

</tbody>