topic Re: Grand total and yearly total are not true in DAX Commands and Tips

Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 18:46:09 GMT

Hi guys,

I want to write dax query that shows sales on weekdays. But grand total and yearly total are not true. Could you help me for solving this problem?

Best regards.

SalesPerWorkingDay = DIVIDE([TotalSales],

SUMX (VALUES ('DimDate'[Datekey].[Year] ),sumx(

VALUES ( 'DimDate'[Datekey].[Month] ),

IF ( [TotalSales] > 0, [WorkingDays] )

)

Re: Grand total and yearly total are not true

tamerj1 — Tue, 05 Jul 2022 19:19:17 GMT

Hi AlfredoBIS

try to wrap the inner SUMX with CALCULATE

Re: Grand total and yearly total are not true

rodrigosan — Tue, 05 Jul 2022 19:33:41 GMT

See if it helps you
Assuming that your date dimension table is correctly related to your fact table.
And in your dates dimension table you have a column that identifies whether the day is useful or not.
I suggest the steps and measures below:

WorkDays =
CALCULATE(
COUNT('📅dCalendar'[Work Day Value]),
'📅dCalendar'[Work Day Value] = 1)

SalesPerWorkingDay =
DIVIDE([Sales Amount] , [WorkDays])

Re: Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 19:34:51 GMT

Hi,

Thank you so much for your answer. I used calculate but doesnt change result

SalesPerWorkingDay = DIVIDE([TotalSales],

(SUMX (VALUES ('DimDate'[Datekey].[Year] ),calculate(sumx(

VALUES ( 'DimDate'[Datekey].[Month] ),

IF ( [TotalSales] > 0, [WorkingDays] )

)

)))

Re: Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 19:53:27 GMT

Thank you so much.

Now

WorkingDays =

SUMX (

'DimDate',

IF ( 'DimDate'[IsWorkDay] = "WorkDay", 1, 0 )

)

But I tried

Workdays2 = CALCULATE(count(DimDate[CalendarYear]),DimDate[IsWorkDay]="WorkDay")

This gave error for this

SalesPerWorkingDay = DIVIDE([TotalSales],

SUMX (VALUES ('DimDate'[Datekey].[Year] ),sumx(

VALUES ( 'DimDate'[Datekey].[Month] ),

IF ( [TotalSales] > 0, [WorkingDays] )

)

Re: Grand total and yearly total are not true

tamerj1 — Tue, 05 Jul 2022 19:57:05 GMT

AlfredoBIS

Better to have year and month columns.then you can iterate over SUMMARIZE ( DimDate, DimDate[Year], DimDate[Month] ). It should work.

Re: Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 20:13:03 GMT

Hi I tried this: but couldnt solve yearly and grand total problem.

WorkingDays =

SUMX (

'DimDate',

IF ( 'DimDate'[IsWorkDay] = "WorkDay", 1, 0 )

)

TotalSales = SUMX ( FactSales, FactSales[SalesQuantity] * FactSales[UnitPrice])

SalesPerWorkingDay8 = DIVIDE([TotalSales],

SUMX (VALUES ('DimDate'[CalendarYear] ),sumx(

VALUES ( 'DimDate'[CalendarMonth] ),

IF ( [TotalSales] > 0, [WorkingDays] )

)

Re: Grand total and yearly total are not true

rodrigosan — Tue, 05 Jul 2022 20:28:59 GMT

I don't understand why you are using SUMX.
your goal is not to know how many working days?
That's why I suggested using COUNT.
You just have a column in your date dimension table that identifies the date as useful or not.

Re: Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 20:44:38 GMT

Hi rodrigosan,

I tried for calculating workdays like this. But result doesnt change. I want to correct grand total sales and yearly total sales.

Workdays2 = CALCULATE(count(DimDate[CalendarYear]),DimDate[IsWorkDay]="WorkDay")

Re: Grand total and yearly total are not true

rodrigosan — Tue, 05 Jul 2022 21:02:16 GMT

You can share a picture of your relationships.
Based on them I believe we can find a solution.

Re: Grand total and yearly total are not true

rodrigosan — Tue, 05 Jul 2022 21:14:45 GMT

You can share a picture of your relationships.
Based on them I believe we can find a solution.

Re: Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 21:20:00 GMT

Sure,

DimDate Table

FactSatles table

relationship:

Re: Grand total and yearly total are not true

rodrigosan — Tue, 05 Jul 2022 21:22:07 GMT

Transform date/time columns to date
Date must be related only to date

Re: Grand total and yearly total are not true

AlfredoBIS — Tue, 05 Jul 2022 21:33:43 GMT

ı changed datakey format to date.

But result didnt change . Year 2017i2018,2019 and grand total is not true

Re: Grand total and yearly total are not true

rodrigosan — Tue, 05 Jul 2022 21:36:59 GMT

You can send a picture of your relationship just like I am sending:

Re: Grand total and yearly total are not true

AlfredoBIS — Wed, 06 Jul 2022 22:35:26 GMT

Hi,

I couldnt solve it yet 😕

Re: Grand total and yearly total are not true

rodrigosan — Thu, 07 Jul 2022 01:11:17 GMT

You can send a picture of your relationship just like I am sending:

Re: Grand total and yearly total are not true

v-easonf-msft — Mon, 08 Aug 2022 09:48:43 GMT

Hi, AlfredoBIS

Has the problem been solved so far?

If you still need help, please share a sample file for further research.

Best Regards,
Community Support Team _ Eason