Showing the most common value in a subset of data in a table

[NeuroNiky]

Hello,

I am creating a table that shows some group up data using basically this format:

Customer	Article	Quantity	ProductiveCycle
1234	ABC	20	SFLSTN
5678	XYZ	30	SFEOFL

This table is getting generated by using the data of all orders historically done for various articles.

The orders are not equal though, and so they have different ProductiveCycle values, in example:

Customer	Article	Quantity	ProductiveCycle
1234	ABC	2	SFLSTN
1234	ABC	2	SFLSTN
1234	ABC	1	SFLSTN
1234	ABC	5	SFLSTNFK
1234	ABC	5	SFLSTNFK
1234	ABC	5	SFLSTNFKFL

Is there a way in the main table (a PowerBI matrix) to show the most common value from the database data, ie the one for which I've received more orders for that combination of customer and article (in this example SFLSTN, as it appears in 3 different orders) and/or the one for which I produced the highest total quantity summing up all orders for the same customer and article (in this example SFLSTNFK, as the total ordered quantity is 10, greater than the sum of ordered quantity for all other ProductionCycle values)?

I could do this in Oracle directly, but I'm trying not to do any pre-grouping of the order data so that I can get them easily from different sources.

Thanks a lot for your help, best regards,

Niky

[NeuroNiky]

Thank a lot! Works like a charm!

[v-yangliu-msft · Accepted solution]

Hi  @NeuroNiky ,

Here are the steps you can follow：

1. Create calculated column.

Count_column = COUNTX(FILTER(ALL('Table'),'Table'[Customer]=EARLIER('Table'[Customer])&&'Table'[Article]=EARLIER('Table'[Article])&&'Table'[ProductiveCycle]=EARLIER('Table'[ProductiveCycle])),[ProductiveCycle])

2. Create measure.

Flag =
var _count=
COUNTX(FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Article]=MAX('Table'[Article])&&'Table'[ProductiveCycle]=MAX('Table'[ProductiveCycle])),[ProductiveCycle])
var _max=MAXX(ALL('Table'),[Count_column])
return
IF(
    MAX('Table'[Count_column])=_max,1,0)

Sum_Measure =
SUMX(
    FILTER(ALL('Table'), 'Table'[Customer]=MAX('Table'[Customer])&&'Table'[Article]=MAX('Table'[Article])&&'Table'[ProductiveCycle]=MAX('Table'[ProductiveCycle])),
    [Quantity])

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly