Calculate for each row a filtered sum result depending on the code number of the row

[Baelya]

Hi All !

Here is an exemple of the data I'm working on : 

So, each row represents the remaining quota for a person X ordering units of product code XX in month AAAAMM, based on their given quota.

However, I'd like to introduce an additional perspective:

Let's say it's April 2023, 202304. The order for 110 pcs by person A will only be partially allocated: since there was a remaining quota of 65 pcs, 45 pcs will remain unallocated.

However, looking at future quota quantities in the coming months, we can anticipate that the current order will be fully allocated in the future. I'd like to incorporate this perspective into my dashboard.

So, there are multiple possiblities to express that. Perhaps the simpliest way would be to take the quota left of MAX(AAAAXX) for each person, each code product. But I did not find a way to express that in DAX.

It's equivalent to calculate SUM(Qty Quota)-SUM(Qty Order) for each row having the same Code and the same Person. 

I never had such a situation before. Between a calculted measure and a calculeted column, I would prefer the latter (if the measured one is easy, I would also be curious to see it!). At the end of the end, I would like to have such a PowerBI table with the additional "Total Quota Left" column : 

I don't see any way for me to code that, and more particularly, I don't know of a way to say to DAX : "SELECT rows with same (code,person) and then calcule the difference between SUM(Qty Quota) - SUM(Qty order)...."

Any help be will be much appreciated ! 

Thanks

[Baelya]

Ok, so I have tried this code instead to understand this ALLEXCEPT detail : 

Total Quota Left =
VAR CurrentPerson = Orders[Person]
VAR CurrentCode = Orders[Product Code]
RETURN
CALCULATE(
SUM(Orders[Qty Quota]) - SUM(Orders[Qty Order]),
FILTER(Orders, Orders[Person] = CurrentPerson),
FILTER(Orders, Orders[Product Code] = CurrentCode),
)

It seems that this version is also valid. 

[Baelya]

I've tested the same code without the ALLEXCEPT filter part and it does indeed not provide the correct result but I still don't understand why.
Can someone help understand please ? 

[123abc · Accepted solution]

To achieve the desired result of calculating the "Total Quota Left" column in Power BI based on the remaining quota for each person and product code, you can indeed use DAX expressions. You can create a calculated column in Power BI to compute this value. Here's how you can do it:

Assuming you have a table named Orders with columns Person, Product Code, Month, Qty Quota, and Qty Order, you can follow these steps:

1. In Power BI, go to the data model view.

2. Select your table (e.g., Orders).

3. Go to the 'Modeling' tab and click on 'New Column'.

4. Use the following DAX expression to create the calculated column:

Total Quota Left =
VAR CurrentPerson = Orders[Person]
VAR CurrentCode = Orders[Product Code]
RETURN
CALCULATE(
SUM(Orders[Qty Quota]) - SUM(Orders[Qty Order]),
ALLEXCEPT(Orders, Orders[Person], Orders[Product Code]),
Orders[Person] = CurrentPerson,
Orders[Product Code] = CurrentCode
)

This DAX expression calculates the difference between the sum of the quota and the sum of the orders for each combination of person and product code.

The ALLEXCEPT function removes all filters from the table except for the specified columns (Person and Product Code), ensuring that the calculation is performed at the granularity of each person and product code combination.

This calculated column will provide the "Total Quota Left" value for each row in your table, considering the specific person and product code combination.

After adding this calculated column, you should see the "Total Quota Left" column populated with the desired values in your Power BI table.

If you prefer to use a measure instead of a calculated column, you can also achieve this with similar logic using a measure. However, calculated columns tend to be more efficient for this type of calculation as they pre-calculate values during data refresh.

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.

[Baelya]

Many thanks !! 

Have a question to help me understand how DAX works : if I understand correctly, DAX will iterate each row and first stock the variables CurrentCode & CurrentPerson. Then, you add the filter on the table to restrict it only to the rows with the CurrentCode value & CurrentPerson of the current row that DAX is treating. So why is necessary to add to the ALLEXCEPT(Orders, Orders[Person], Orders[Product Code]) filter here ? Thanks