Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Len30
Frequent Visitor

Measure to sum by unselected column

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

 

ProductYearCustomerAmount
AAA2023Bob10
AAA2023Bob10
AAA2023Sally50
AAA2024James50
AAA2024Jack80
BBB2023Bob20
BBB2024Bob20
BBB2024Bob20
BBB2024James20
CCC2023Sally10
CCC2023Sally20
CCC2024James30

 

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;

 

ProductYearAmount sumSmallest amount by customer
AAA20237020
AAA202413050
BBB20232020
BBB20246020
CCC20233030
CCC20243030

 

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;

 

ProductAmount sumSmallest amount by customer
AAA20020
BBB8020
CCC6030

 

Any help would be much appreciated.

1 ACCEPTED SOLUTION
v-xiandat-msft
Community Support
Community Support

Hi @Len30 ,

Below is my table:

vxiandatmsft_0-1709098184652.png

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:

vxiandatmsft_1-1709098288457.png

The final output is shown in the following figure:

vxiandatmsft_4-1709098450899.pngvxiandatmsft_5-1709098459821.png

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.

View solution in original post

2 REPLIES 2
Len30
Frequent Visitor

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.

v-xiandat-msft
Community Support
Community Support

Hi @Len30 ,

Below is my table:

vxiandatmsft_0-1709098184652.png

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:

vxiandatmsft_1-1709098288457.png

The final output is shown in the following figure:

vxiandatmsft_4-1709098450899.pngvxiandatmsft_5-1709098459821.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.