Search code examples
excelexcel-formulapivot-chart

In Excel Pivot chart, I wanted to show the only the max amount transferred to customers in a single day (sum of all the transaction in a day)

Here is the sample excel input data & output in a chart I should be able to see the sum of transaction happened on 5th as that's the maximum amount.

Any help or lead will be highly appreciable.

enter image description here

Solution

  • Not sure about how you would do this using PIVOT CHART (Post edited, with help suggestion -- check below), but here is an example of using EXCEL FORMULAS to achieve using dynamic array formulas available in MS365.

    enter image description here

    • Formula used in cell F2

    =LET(
     a, UNIQUE(D2:D7),
     b, MMULT(N(a=TOROW(D2:D7)),C2:C7),
     c, FILTER(HSTACK(a,b),MAX(b)=b),
     VSTACK({"Date","Amount Paid"},c))

    Well, I tried to follow as suggested by Jos Woolley Sir, and it gives the exact output as shown using the formula above.

    Here are the steps:

    • First place the Dates in Rows area and Amount Paid in Values as summarized as sum.

    enter image description here

    • Next click on Date Field --> Value Filters --> Top 10 and change the number 10 to 1.

    enter image description here

    • To get desired output.

    enter image description here

    For more efficient performance using Excel Formulas use SUMIFS() instead of MMULT() as suggested by Jos Sir in comments below.

    enter image description here

    • Formula used in cell F2

    =LET(
     a, UNIQUE(D2:D7),
     b, SUMIFS(C2:C7,D2:D7,a),
     c, FILTER(HSTACK(a,b),MAX(b)=b),
     VSTACK({"Date","Amount Paid"},c))