Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
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.
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |