DAX measure to filter only one row with lowest number per customer

[kokos88]

Hi,

I have such dataset:

Customer	Reason	Amount	Row Number
1	A	7	1
1	B	15	2
1	C	35	3
2	B	13	1
2	C	9	2
2	D	22	3
3	C	1	1
3	A	24	2
3	B	17	3

I have added filter: "Reason" to the report (values are: A, B, C, D).

I need measure which calculates SUM of Amount column for all customers, but it should only take one row per customer - with the lowest number of "Row Number".

Let's imagine: I have choosen on "Reason" slicer: B and C. So my measure should take only (values in orange in above table):

For Customer 1, Amount: 15 - because the lowest Row Number is 2 - for "Reason" B,

For Customer 2, Amount: 13 - because the lowest Row Number is 1 - for "Reason" B,

For Customer 3, Amount: 1 - because the lowest Row Number is 1 - for "Reason" C

So total value of this measure is 15 + 13 + 1 = 29

Can anybody help me with this? Thank you!

[v-kathullac]

Hi @kokos88 ,

Thank you for reaching out to Microsoft Fabric Community.

I hope the below screenshot will solve your issue .

Attaching the pbix file for your refrence let me know if you need any further assistance.

Thanks.

[rohit1991]

Hi @kokos88 ,
Heres a DAX measure that will return the sum of Amount per customer, only for the row with the lowest "Row Number" (after applying any Reason filter):

Sum of Amount - Lowest Row per Customer = 
SUMX(
    VALUES('Table'[Customer]),
    VAR MinRow = CALCULATE(
        MIN('Table'[Row Number])
    )
    RETURN
        CALCULATE(
            SELECTEDVALUE('Table'[Amount]),
            'Table'[Row Number] = MinRow
        )
)

Replace 'Table' with your actual table name.

[kokos88]

Thank you for you solution. It works 🙂

I have one more question... when I create table with customer and reason - sum of values from all rows is different than the measure you created. 
Any idea what filter I can add to the visual to only show one row for customer? So, to show only rows highlighted in green?

[v-achippa]

Hi @kokos88,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @rohit1991 and @techies for the prompt response.

To also make the table visual to show only one row per customer, create a helper measure that filters out all other rows like below:

Show Lowest Row Only =

VAR MinRow =

    CALCULATE(

        MIN('Table'[Row Number]),

        ALLEXCEPT('Table', 'Table'[Customer]),

        KEEPFILTERS('Table'[Reason])

    )

RETURN

    IF('Table'[Row Number] = MinRow, 1, 0)

Add this measure to your table and apply a visual level filter like Show Lowest Row Only is 1

Now the table shows only the row with the lowest Row Number for each customer and the sum will match the measure result like the green rows which are highlighted.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[kokos88]

Hi,

Thank you! When I try to add this measure there is error message:

A single value for column 'Row Number' in table 'Table' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[rohit1991]

Hi @kokos88 

To always show only the row with the lowest Row Number per customer (after Reason slicer/filter), add this measure to your table:

Show Lowest Row Only =
VAR ThisCustomer = 'Table'[Customer]
VAR MinRow =
    CALCULATE(
        MIN('Table'[Row Number]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Customer] = ThisCustomer
        )
    )
RETURN
    IF('Table'[Row Number] = MinRow, 1, 0)

Then, add this measure to your table visual and set a visual-level filter to Show Lowest Row Only = 1. This should update correctly with your Reason slicer and always keep only the lowest row per customer.

[kokos88]

Thank you for your response!

Unfortunately the solution doesn't work...

When I did as you proposed the table shows different value than expected - one row is missing. 

I have selected in slicer Reason B and C

There should be additional 1 row (highlighted in green). But column isLowestRow is 0 for this case, that's why it is not present

[techies]

Hi @kokos88 please try this

Sum_Lowest_RowPerCustomer =

SUMX(

    VALUES(DATAS[Customer]),

    VAR CustomerID = DATAS[Customer]

    VAR MinRow =

        CALCULATE(

            MIN(DATAS[Row Number]),

            DATAS[Customer] = CustomerID

        )

    RETURN

        CALCULATE(

            SUM(DATAS[Amount]),

            DATAS[Customer] = CustomerID,

            DATAS[Row Number] = MinRow

        )

)

[kokos88]

Thank you for you solution. It works 🙂

I have one more question... when I create table with customer and reason - sum of values from all rows is different than the measure you created. 
Any idea what filter I can add to the visual to only show one row for customer? So, to show only rows highlighted in green?