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
lost_flower
Helper III
Helper III

wrong total

Hello,

my total for "Lost_amount" is shown as average instead of a summary.

lost_flower_0-1677351232640.png

It is a measure:

Lost_amount =

VAR Purchase=CALCULATETABLE(VALUES('6_Lost'[Key]),
FILTER(ALL('Date'),
'Date'[Date] >= MIN('Date'[Date]) - 'Month'[Month Value]*30 &&
'Date'[Date] <= MAX('Date'[Date])))

VAR _Current=CALCULATETABLE(VALUES('6_Lost'[Key]),
FILTER(ALL('Date'),
'Date'[Date] >= MIN('Date'[Date]) //-'Month'[Month Value]*30 &&
'Date'[Date] <= [Min]('Date'[Date])))

RETURN
CALCULATE(
CALCULATE('6_Lost'[Last_Sales], DATESBETWEEN('Date'[Date],MIN('Date'[Date])-'Month'[Month Value]*30, MIN('Date'[Date]) )),
EXCEPT(Purchase,_Current))


I am not sure how I can fix this. Does anyone has an idea?

Thanks

 

1 ACCEPTED SOLUTION

@lost_flower 

Still can be addef to the SUMMARIZE table

Lost_new =
SUMX (
SUMMARIZE(
'6_Lost'
'6_Lost'[StartDate],
'6_Lost'[EndDate],
'6_Lost'[Product],
'6_Lost'[CYname],
'Date'[Jahr],
'Date'[Monat]
),

View solution in original post

13 REPLIES 13
tamerj1
Super User
Super User

Hi @lost_flower 

please try

Lost_amount =
SUMX (
SUMMARIZE (
'6_Lost',
'6_Lost'[StartDate],
'6_Lost'[EndDate],
'6_Lost'[Product],
'6_Lost'[CYname]
),
CALCULATE (
VAR Purchase =
CALCULATETABLE (
VALUES ( '6_Lost'[Key] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date]
>= MIN ( 'Date'[Date] ) - 'Month'[Month Value] * 30
&& 'Date'[Date] <= MAX ( 'Date'[Date] )
)
)
VAR _Current =
CALCULATETABLE (
VALUES ( '6_Lost'[Key] ),
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= MIN ( 'Date'[Date] )
&& 'Date'[Date] <= CALCULATE ( [Min], 'Date'[Date] )
)
)
RETURN
CALCULATE (
CALCULATE (
'6_Lost'[Last_Sales],
DATESBETWEEN (
'Date'[Date],
MIN ( 'Date'[Date] ) - 'Month'[Month Value] * 30,
MIN ( 'Date'[Date] )
)
),
EXCEPT ( Purchase, _Current )
)
)
)

Hi, 

 

thanks for your help. I have tried it and add another column "Lost_new", but it seems to be empty 😞

lost_flower_0-1677353856502.png

 

@lost_flower 

Can you please share the code that you have used? What columns are placed in the table visual?

My fault. Indeed it was not complete. Two columns are in the visual too, which come from a date table:

lost_flower_0-1677354955473.png

 

@lost_flower 

You need to add these two columns to the SUMMARIZE table

Do you mean in the SUMMARIZE code? 

 

Lost_new =
SUMX (
SUMMARIZE ('Date','Date'[Jahr],'Date'[Month_txt],
'6_Lost'
'6_Lost'[StartDate],
'6_Lost'[EndDate],
'6_Lost'[Product],
'6_Lost'[CYname]
),
 

These two dates comes from another table and I cannot add them. two tables in summarize code, is this possible?
Sorry, I am still learning DAX and I think I have add it wrong, because the code is not working. 

@lost_flower 

Ok can you olease share a screenshot of the data model view to see the relationships.

sure, here you go:

lost_flower_0-1677358518465.png

 

@lost_flower 

Still can be addef to the SUMMARIZE table

Lost_new =
SUMX (
SUMMARIZE(
'6_Lost'
'6_Lost'[StartDate],
'6_Lost'[EndDate],
'6_Lost'[Product],
'6_Lost'[CYname],
'Date'[Jahr],
'Date'[Monat]
),

Hi  @tamerj1 ,

I have created the quick measure to calculate the moving average with dynamic slicer. But facing column total error for the measure. Like it has to sum Jan to Dec data and show it in the column but column total only taking Dec month data everytime.

 

Example - Should be Oct+Nov+Dec = 75379+62685+26346 = 164410 But the total showing is 26346.

 

Please help me with the following issue

I am getting column total error and taking Matrix table 

Rows - Country,ProductCategory

Column - Month

anushaghi123_0-1694600161712.png

 

 

 

I'm getting column total incorrect for Forecast(Blue) data.

 

Dax Formula (Quick Measure) - Product_Count rolling average =
IF(
ISFILTERED('Table_name'[dimdate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('Table_name'[dimdate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Table_name'[dimdate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, 'Moving Average'[Moving Average Value], MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Table_name'),
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Table_name'[Product_Count]),
ALL('Table_name'[dimdate].[Day])
)
)
)

 

Thanks

Hi @anushaghi123 

please try

Product_Count rolling average correct total =
SUMX (
SUMMARIZE (
'Table_name',
'Table_name'[dimdate].[Year],
'Table_name'[dimdate].[QuarterNo],
'Table_name'[dimdate].[Quarter],
'Table_name'[dimdate].[MonthNo],
'Table_name'[dimdate].[Month]
),
[Product_Count rolling average]
)

Thanks so much @tamerj1 . It is working but now only showing till current month not the forecast data for October month like below.

anushaghi123_0-1694674885551.png

 

Oh, wow. I did not suspect that it would make a difference by putting it at the end. It works now. 

 

Thanks so much for your help. Really appreciate it 🙂

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.

Top Solution Authors