Measure to sum by unselected column

[Len30]

I’m having an issue creating a desired measure. I have a table in this format;

Product	Year	Customer	Amount
AAA	2023	Bob	10
AAA	2023	Bob	10
AAA	2023	Sally	50
AAA	2024	James	50
AAA	2024	Jack	80
BBB	2023	Bob	20
BBB	2024	Bob	20
BBB	2024	Bob	20
BBB	2024	James	20
CCC	2023	Sally	10
CCC	2023	Sally	20
CCC	2024	James	30

In my draft dashboard, this table is summarised in a table visualisation. I want to create a measure that presents the smallest amount by the selected fields, and also by customer.

So the desired visualisation table would be;

Product	Year	Amount sum	Smallest amount by customer
AAA	2023	70	20
AAA	2024	130	50
BBB	2023	20	20
BBB	2024	60	20
CCC	2023	30	30
CCC	2024	30	30

Slicers exist for the product, year and customer fields. And the fields in the visualisation can be changed by the user. So as another example, if year is not selected, I’d want the visualisation to read;

Product	Amount sum	Smallest amount by customer
AAA	200	20
BBB	80	20
CCC	60	30

Any help would be much appreciated.

[Len30]

Thanks v-xiandat-msft. This produces the outcome I asked for, so I’ve marked it as the solution.

Unfortunately, I’ve realised my example doesn’t highlight one of the key issues. In the example data, calculating the table by product can be done by the minimum values of “Measure” in the table by product and year. In reality, I can’t make this assumption. If the value for BBB / 2024 / James was 100 rather than 20, this would show the issue – the values in the last table for BBB would then need to resolve as 160 and 60.

[Anonymous · Accepted solution]

Hi @Len30 ,

Below is my table:

The following DAX might work for you:

column = 
  IF('Table'[Customer] = "Sally" && 'Table'[Product] = "CCC",
    CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Product] = "CCC") ,FILTER('Table','Table'[Customer] = "Sally")),CALCULATE(SUM('Table'[Amount])))


Measure = CALCULATE(
    MIN('Table'[column]),
     ALL(Parameter[Parameter]))


Amount Sum = CALCULATE(
      SUM('Table'[Amount]),
      ALL(Parameter[Parameter]))

Then create a Field parameter:

The final output is shown in the following figure:

Best Regards,

Xianda Tang

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