topic Wrong sum total table of measure column in DAX Commands and Tips

Wrong sum total table of measure column

danirc — Tue, 20 Oct 2020 13:34:37 GMT

Good afternoon,

I have a problem with a measure created in dax. I have an application that creates date calendars and each date you can assign a comparable date. Multiple dates can have the same comparable date. When this happens, the power BI tables only add the sale of one date repeated because it detects that it is the same foreign key.

I give you an example and the measure:

TotalNetComparableDate = CALCULATE(SUM(orders[net]),USERELATIONSHIP(orders[calendarFK],calendars_days[calendarComparableFK]))

Column comparableSale is TotalNetComparableDate measure

date	comparableDate	netSale	comparableSale
11/10/2020	13/10/2019	2.767,93 €	2.071,68 €
12/10/2020	13/10/2019	2.875,71 €	2.071,68 €
TOTAL		5.643,64 €	2.071,68 €

Re: Wrong sum total table of measure column

danirc — Tue, 20 Oct 2020 13:38:45 GMT

Re: Wrong sum total table of measure column

Anonymous — Wed, 21 Oct 2020 13:32:24 GMT

danirc Hey Mate ,

You can try this .hope this will work for you ,

TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
CROSSFILTER(orders[calendarFK],
calendars_days[calendarComparableFK],
Both
)
)
You can give kudos as well if this work and accept this as a solution .

Thank you

Re: Wrong sum total table of measure column

danirc — Wed, 21 Oct 2020 16:21:16 GMT

Thanks for the help but I get this. Net sales of actual year is obtained in the column of comparable sales.

Re: Wrong sum total table of measure column

Anonymous — Wed, 21 Oct 2020 17:08:13 GMT

danirc
can you share your previous dax formula which you use to calculate comparible sale .

how will you get the comparible date .

TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
CROSSFILTER(orders[calendarFK],
calendars_days[calendarComparableFK],
Both
)
,values(calendars_days[calendarComparableFK]))
or
TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
USERELATIONSHIP(orders[calendarFK],
calendars_days[calendarComparableFK]
)
,all(calendars_days[calendarComparableFK]))

or

TotalNetComparableDate =
CALCULATE(SUM(orders[net]),
USERELATIONSHIP(orders[calendarFK],
calendars_days[calendarComparableFK]
)
,Values(calendars_days[calendarComparableFK]))

or
can you share a sample pbix file
that will great to understand the requirement

You can give kudos as well if this work and accept this as a solution .

Thank you

Re: Wrong sum total table of measure column

danirc — Thu, 22 Oct 2020 14:07:26 GMT

Thanks for your help, I have tried all the solutions but it returns the same result.
I'll explain the table structure to see if we can find the solution.
I have a stores table that is related to an orders table.
Each order is linked by the id of the store with the table of stores.
I also have a calendar table in which each store has its calendar and each day of the calendar has a comparable date in the same table.
Finally I have created a foreign key creating a new column joining the date with the store id in the orders table and the same in the calendar table.
In this way I have related the orders with their dates in the calendar, but to obtain the orders of the comparable dates I create the same relation but in the calendars table I create the foreign key with the comparable dates.

I can not pass you the pbx because it contains many more relationships, I give you images of this specific problem.
This image is from the calendar table, it relates to the orders table through calendar-> calendarFK to orders -> calendarFK and another inactive relation: calendar-> comparableCalendarFK to orders -> calendarFK.

Thank you very much.

Re: Wrong sum total table of measure column

Anonymous — Thu, 22 Oct 2020 15:59:53 GMT

danirc Hey Mate ,
try this then

CALCULATE (

SUM (orders[net]),

TREATAS (

VALUES ( ‘orders’[calendarFK]),

calendars_days[calendarComparableFK]
)

)

You can refer Below link for creating virtual relationship.

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/

Re: Wrong sum total table of measure column

danirc — Thu, 22 Oct 2020 20:36:34 GMT

I didn't know about that function, thank you.

I have tried it but it returns empty column.

Re: Wrong sum total table of measure column

danirc — Thu, 12 Nov 2020 12:25:32 GMT

Doesn't anyone have a valid solution?

Re: Wrong sum total table of measure column

wdx223_Daniel — Fri, 13 Nov 2020 02:31:43 GMT

TotalNetComparableDate = CALCULATE(SUM(orders[net]),calendars_days[calendarFK] IN VALUES(calendars_days[calendarComparableFK]))

Re: Wrong sum total table of measure column

danirc — Mon, 16 Nov 2020 16:07:09 GMT

Comparable sell column appears empty.