DAX measure counting the most common sum of payments for a product

[dmytro_po]

Hi!

Could you please help me to write a measure wich would calculate the most common sum of payments for each product.

For Product 1 I expest to see 300 and for Product 2 I expecet to see 500.

Thank you in advance for your help.

[Zubair_Muhammad]

HI @dmytro_po

Try this solution

First Add a MEASURE that will count the Sum of Payments for each product

CountPayments =
VAR Currentsum =
    SUM ( TableName[Payment] )
VAR CurrentProduct =
    SELECTEDVALUE ( TableName[Product] )
RETURN
    COUNTROWS (
        FILTER (
            FILTER (
                ALL ( TableName[Product], TableName[Customer] ),
                TableName[Product] = CurrentProduct
            ),
            CALCULATE ( SUM ( TableName[Payment] ) = Currentsum )
        )
    )

You will get this

Now RANK the Count of Payments for each Product with this MEASURE

RankPayments =
RANKX (
    ALL ( TableName[Product], TableName[Customer] ),
    [CountPayments],
    ,
    DESC,
    DENSE
)

You will get

[dmytro_po]

Hi @Zubair_Muhammad,

thanks for your help.

However, as the end result, I would like to receive the following table:

Meaning, without the need of showing customers and sum of payments.

[Zubair_Muhammad · Accepted solution]

Hi @dmytro_po

Here is a one step solution. It combines the MEASURES above

see the file here

Go to Modelling Tab>>> New Table

MyTable =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            ADDCOLUMNS (
                SUMMARIZE (
                    TableName,
                    TableName[Product],
                    TableName[Customer],
                    "MostCommonCost", SUM ( TableName[Payment] )
                ),
                "CountPayments",
                VAR Currentsum =
                    CALCULATE ( SUM ( TableName[Payment] ) )
                VAR CurrentProduct =
                    CALCULATE ( SELECTEDVALUE ( TableName[Product] ) )
                RETURN
                    COUNTROWS (
                        FILTER (
                            FILTER (
                                ALL ( TableName[Product], TableName[Customer] ),
                                TableName[Product] = CurrentProduct
                            ),
                            CALCULATE ( SUM ( TableName[Payment] ) = Currentsum )
                        )
                    )
            ),
            "RankPayments", RANKX (
                ALL ( TableName[Product], TableName[Customer] ),
                CALCULATE ( [CountPayments] ),
                ,
                DESC,
                DENSE
            )
        ),
        [RankPayments] = 1
    ),
    TableName[Product],
    [MostCommonCost]
)

[Zubair_Muhammad]

@dmytro_po

Now you can add a visual level filter to get the RANK 1 i.e.most common sum of payments for each product.