Highest Basket (Receipt) Value

[shakirkhanani]

Hello, 

I need help in trying to find the highest / Max "Receipt No." baesd on the "Amount" as per the below Data Table: 

Receipt No.	Goods Code	Time	Price	Qty	Amount
201803230001	1410000278	2018-03-23 12:39:55	249.00	1.00	249.00
201803230001	1013000194	2018-03-23 12:39:55	369.00	1.00	369.00
201803230001	1013000195	2018-03-23 12:39:55	369.00	1.00	369.00
201803230001	1611001788	2018-03-23 12:39:55	749.00	1.00	749.00
201803230002	1410000278	2018-03-23 12:42:44	249.00	1.00	249.00
201803230002	1013000194	2018-03-23 12:42:44	369.00	1.00	369.00

Thanks in advance.

[Zubair_Muhammad · Accepted solution]

Hi @shakirkhanani

Try this MEASURE

Measure =
VAR MaxAmount =
    MAXX (
        ALLSELECTED ( Table1[Receipt No.] ),
        CALCULATE ( SUM ( Table1[Amount] ) )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Table1[Receipt No.], 1 ),
        FILTER (
            ALLSELECTED ( Table1[Receipt No.] ),
            CALCULATE ( SUM ( Table1[Amount] ) ) = MaxAmount
        )
    )

[Zubair_Muhammad · Accepted solution]

Hi @shakirkhanani

Try this MEASURE

Measure =
VAR MaxAmount =
    MAXX (
        ALLSELECTED ( Table1[Receipt No.] ),
        CALCULATE ( SUM ( Table1[Amount] ) )
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Table1[Receipt No.], 1 ),
        FILTER (
            ALLSELECTED ( Table1[Receipt No.] ),
            CALCULATE ( SUM ( Table1[Amount] ) ) = MaxAmount
        )
    )

[shakirkhanani]

Brilliant! How can I further expand to show the Total Amount of the Measured Receipt No. 

[Zubair_Muhammad · Accepted solution]

Hi @shakirkhanani

Its there in the variable of the above MEASURE

MaxAmount =
MAXX (
    ALLSELECTED ( Table1[Receipt No.] ),
    CALCULATE ( SUM ( Table1[Amount] ) )
)

[shakirkhanani]

Genius, at least to me.  I wasted the half of my Sunday figuring this out.  Thanks again dear Zohaib.  Appreicate if you could share some pointers to me to improve by BI.

[Zubair_Muhammad]

Hi @shakirkhanani

Don't worry ......With time you will get to grip with Power BI.....

Here are some tips

1) Start solving the problems in this forum

2) Read the books...Following are some good books

• Building Data Models With Power Pivot

• Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Exce...

• Learn to Write DAX: A practical guide to learning Power Pivot for Excel and Power BI

• M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query

• The Definitive Guide to DAX

[Zubair_Muhammad]

@shakirkhanani

Also subscribe to following Power BI blogs and sites... and follow the posts/articles published there

www.powerpivotpro.com by @avisingh and Rob Collie

www.daxpatterns.com  by @AlbertoFerrari and @marcorusso

www.sqlbi.com by abovegentlmen

https://exceleratorbi.com.au/exceleratorblog/ by @MattAllington

https://www.excelguru.ca/blog/ by @KenPuls

http://www.ashishmathur.com/knowledge-base/ by @Ashish_Mathur

[shakirkhanani]

Thank you so much Zohaib for the tips and the books, will definitely check them out.