topic Re: Dynamic total sum of a column minimum in DAX Commands and Tips

Dynamic total sum of a column minimum

RoadRunner2207 — Mon, 20 Nov 2023 10:24:03 GMT

I have created a matrix visual in my powerbi report, same as the demo created using Excel.
Instead of calculating the sum of the minimum formula's outputs, it's taking the minimum value of those values.

How can I write a dax measure where i can apply the minimum function when an item description has been selected and calculating the sum of those values in the total section.

the use case where I want to use this logic is that after grouping the sales for each month in the year 2023, I want to see the minimum sales, average sales, maximum sales.

The minimum function is extracting the total minimum of those months, and not the total for the minimum values of those year

Re: Dynamic total sum of a column minimum

123abc — Mon, 20 Nov 2023 10:38:51 GMT

To achieve the desired result in Power BI, you can create a DAX measure that calculates the sum of the minimum values for each item description when filtering by a specific condition (e.g., a selected year). Here's an example measure that you can use:

TotalMinimumSales =
CALCULATE(
SUMX(
VALUES('YourTable'[ItemDescription]),
CALCULATE(
MIN('YourTable'[Sales]),
ALL('YourTable'[Month]),
'YourTable'[Year] = SELECTEDVALUE('YourTable'[Year])
)
)
)

Here's a breakdown of the measure:

VALUES('YourTable'[ItemDescription]): This part creates a table of unique values in the 'ItemDescription' column.
CALCULATE(MIN('YourTable'[Sales]), ALL('YourTable'[Month]), 'YourTable'[Year] = SELECTEDVALUE('YourTable'[Year])): This part calculates the minimum sales for each 'ItemDescription' within the selected year, ignoring any filters on the 'Month' column.
SUMX(...) wraps the inner calculation to iterate over each 'ItemDescription' and calculate the sum of the minimum sales.

This measure should give you the total sum of the minimum sales for the selected year when you use it in your matrix visual. Replace 'YourTable', 'ItemDescription', 'Sales', 'Month', and 'Year' with the actual names of your table and columns.

Remember to adjust column and table names based on your data model. Additionally, be cautious about the context in which you use this measure, as it may behave differently depending on your report structure and user interactions.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Dynamic total sum of a column minimum

RoadRunner2207 — Mon, 20 Nov 2023 13:13:55 GMT

This solution has a missing step as even though in the use case example i have stated that I'll be using total of the minimum for the whole year, however, sometimes I will select 2 months (October/November) and get the total of their minimum values

Re: Dynamic total sum of a column minimum

sjoerdvn — Mon, 20 Nov 2023 13:51:14 GMT

You should have a date dimesion in your model that includes a YearMonth or MonthYear column, then you could create the measure like this:
MinMonthlySales = SUMX(VALUES(dim_date[year_month]),CALCULATE(MIN(fact_table[sales])))

Re: Dynamic total sum of a column minimum

123abc — Tue, 21 Nov 2023 05:26:53 GMT

I understand the requirement better now. To achieve the flexibility of selecting specific months and getting the total of their minimum values, you can modify the DAX measure to consider the selected months in the filter context. Assuming you have a 'Month' column in your 'Sales' table, you can use the following modified DAX measure:

TotalMinimumSales =
CALCULATE(
SUMX(
VALUES('Sales'[ItemDescription]),
CALCULATE(
MIN('Sales'[SalesAmount]),
ALL('Sales'[Month]) // Remove filters from 'Month' except those selected
)
)
)

In this modification, the ALL('Sales'[Month]) part removes all filters from the 'Month' column except the ones selected in the visual. This way, when you select specific months in your matrix visual, the measure will consider only those months for calculating the minimum sales amount.

Make sure to adjust the column and table names according to your actual data model. This measure should work in scenarios where you select specific months and want to calculate the total of their minimum values for the selected 'ItemDescription'.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.