Need total sum to work dynamically with slicers

[nmrt]

Hello PBI community!

I am trying to get the ABS(sales - Forecast at lag3). Each row gives the correct quantity but the totals do not add up accordingly.

I have an item master and a customer master table that I need to use as slicers to filter through the matrix or charts.

Product ID	Product Name
12345	Product A
23456	Product B

Customer Grouping	Customer Name
Grocery	Customer A
Retail	Customer B

I have a forecast table at a customer and item level which details all the forecast versions submitted and then measures that calculate the Lags (Lag 3 meaning the forecast submitted 3 months ago)

Forecast Submission Version	Item	Customer	Qty
04/2024	Product A	Customer A	100
04/2024	Product A	Customer B	120
03/2024	Product A	Customer A	90

The lag is a measure due to data issues where the customer doesn't appear in all submissions and I could not use the EARLIER function on a calculated column

I also have a sales table which is a live connection from another PBI model

Date	Customer	Product	Qty
04/01/2024	Customer A	Product A	10
04/02/2024	Customer B	Product B	20

I have looked on the forum and youtube but most solutions require calculated columns with SUMX instead of measures or SUMMARIZE function which usually indicates one table and I need to use the Sales table and Forecast table and is not dynamic to each slicer that I apply (item master and/or customer master slicers)

= SUMX(SUMMARIZE(Forecast,'itemMaster'[Product Name],'itemMaster'[Product ID],'Customer Master'[Customer Name],' Customer Master'[Customer Grouping]),ABS([Sales'[Qty]-Forecast' [(Lag3)])) - This totals the quantity correctly but each row is incorrect

Would greatly appreciate your help!

[Greg_Deckler]

@nmrt First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also, you will want to make sure you use ALLSELECTED when you SUMMARIZE.

[nmrt]

@Greg_Deckler Can you show me what the formula would look like if I used SUMMARIZE and ALLSELECTED?

[Greg_Deckler]

@nmrt SUMMARIZE( ALLSELECTED('Table'), ... )

[nmrt]

@Greg_Deckler thanks for your response! I have watched several of your videos on Enterprise DNA. I've tried your solution but it gives me an error

Measure 2 = SUMX(SUMMARIZE(ALLSELECTED(Forecast),'itemMaster'[Product Name],'itemMaster'[Product ID],'Customer Master'[Customer Name],'Customer Master'[Customer Grouping]),ABS([Lag3Difference])) /// does not give the correct qty per row or total

Measure 3 = 

SUMX(SUMMARIZE(ALLSELECTED(dateMaster),dateMaster[fiscMonthKey]),ABS([Lag3Difference])) /// Here I tried using the date master instead as a common master table and it gives the correct row but not the correct total

Lag3AbsError = CALCULATE(SUMX(SUMMARIZECOLUMNS(itemMaster[Product Name]),ABS([Lag3Difference]))) // gives me the correct row and total quantity when I filter to an item level and a customer + item level. However, it does not work when I look at it from a customer level with no item filter or if I want to look at it with another slicer such as by category or aggregated total. I'm trying to get a version of this formula that is dynamic with the slicer selection

I've been stuck on this for days so would greaty appreciate your help!

[Greg_Deckler]

@nmrt In the image you are showing, what non-summarized columns are in that table visual? 

[nmrt]

@Greg_Deckler all columns are measures

PO Qty = SUM(Sales[Qty]) I've also tried SUMX(Sales,Sales[Qty])

Lag3= 

VAR Lag3Var = CALCULATE(SUM(Forecast[Qty]),Forecast[Forecast Submission Version]=EDATE(MAXX(Forecast,Forecast[Forecast Submission Version]),-3))

RETURN IF(MAXX(Forecast,Forecast[FiscMonthSub])=MAXX(Forecast,Forecast[FiscWeek]),Lag3Var,BLANK()) // This is the forecast submission 3 months ago, bucketed in the same fiscal month

Lag3Difference = [PO Qty]-[Lag3]
I've also created a sample file below

Google Drive 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[nmrt]

@lbendlin Thanks for responding! I've made a sample dataset on the link below:

Google Drive 

[lbendlin]

then measures that calculate the Lags (Lag 3 meaning the forecast submitted 3 months ago)

Your sample data doesn't support your approach using EDATE(-3)

Please explain what your definition of "submitted 3 months ago"  is.

[nmrt]

Hi @lbendlin, the lag calculation is if I am looking at the forecast for March:

Lag0: Would be the March forecast submission for the fiscal month of March

Lag1: Would be the February forecast submission for the fiscal month of March

Lag2: Would be the January forecast submission for the fiscal month of March

Lag3: Would be the December forecast submission for the fiscal month of March

Our forecast is in weeks so we needed to find the fiscal month and fiscal forecast week to match

I want to add too that I could not use EARLIER since we had missing values in the data such as missing forecasts for customers in prior submissions which would give the wrong value.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...