Wrong total values

[MikeUS]

Dear Community, have the following table below and receive the wrong column values. hope someone can help to fix that calculation. 

DATE YEAR = Time Table

DATE Month = Time Table

DATA 1 = DATA from Sales Report (= Total Sales Quantity)

DATA 2 = DATA from Sales Report (= Total Sales)

DATA 3 = Measure ( Sales Price YTD = DATA 2 / DATA 1)

DATA 4 = Measure (Sales Price Last YEAR = CALCULATE([Sales Price YTD],  DATEADD('Time Table'[Date], -1,YEAR))

DATA 5 = Measure (DATA 4 - DATA 3)

DATA 6 = Measure (DATA 5 x DATA 1)       -- Sales Price Savings or Increase versus last year

 today the total for my colmuns with Measures doesnt really makes sense. 

Date YEAR	Date Month	ITEM	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6
2023	March	total	2700	60500	22,41	18,00	-4,41	-11900	wrong
		ITEM 1	500	10000	20	15	-5	-2500
		ITEM 2	1000	10000	10	8	-2	-2000
		ITEM 3
		ITEM 4
		ITEM 5
		ITEM 6	300	1500	5	7	2	600
		ITEM 7
		ITEM 8
		ITEM 9	400	24000	60	55	-5	-2000
		ITEM 10
		ITEM 11
		ITEM 12
		ITEM 13	500	15000	30	25	-5	-2500
		total	2700	60500	22,41	19,30	-3,11	-8400	right

the right total is actually shown in the last row of the table. (manually add to show the target i'm aiming for)

thanks

Mike

[ToddChitt]

You have to be careful how you visualize your data. Yoiu are showing me data from two different years and expecting the final measure (Increase or Decrease from the revious Year) to reflect ONLY the current year. Not so! It is aggregating the two years together. Once you have the two number for the DIVIDE function and know they are calculating correctly, then slice or filter your visual by Year. Try this: Add the Year componenet into the Rows portion of the Matrix and I bet for 2023 is should be close. 

One other thought: It *may* be considering all of 2022 against 8 months 2023!

[ToddChitt]

>>

Problem: the total amount per year is not correct 

Correct is 5443 on line level

Total 6998 aggregate isnt correct<<

I don't see valued 5443 or 6998 on the graph. Not sure what you are talking about. 

Let's boil this down to the elements and a very small time slice. Please put together a TABLE visual (NOT a matrix) and filter it for two years, filter it for one month (like January) and for a few days. I want to see the first 5 days of January 2022 and the first five days of January 2023, 10 days total. Then add [Quantity] and [Quantity Last Year].

Does the [Quantity] for January 1, 2022 = [Quantity Last Year] for January 1, 2023? 

[MikeUS]

i have marked the values 5443 and 6998 in the first screenshot 

below the table you ask for: 

Comment 1) my sales query (=excel database) doesnt allow to see sales per day (MO - SU). i see consolidated one day per months - what is always the first day per months. so dont wonder why you see always DAY 1. 

Comment 2) i have selected March and April as well, as Jan and Feb was a bad month without sales for that particulare item. numbers still line up (Quantity total this year vs. Quantity last year)

Comment 3) the table on the bottom is the one you ask for, the one in the top 

Comment 4) when i select more months Jan - June, numbers dont line up in the table on the bottom, what makes sense as we compare sales volume 2022 and 2023 and that was different. problem i see - my increase and decrease value is incorrect (marked in red)

[ToddChitt]

I think you need an intermediate measure: 

Total Sale Amount YoY] = [Total Sale Amount] - [Total Sale Amount LAST YEAR]

Does THAT number track for every month?

[MikeUS]

that YOY value is consistent 40197

[ToddChitt]

Do you have a Date Dimension? Is it tied to your fact data on the Date? If not, let's start there.

Create a table with this:

My Dates = CALENDAR("01/01/2020", "12/31/2024")

Add to that a calculated column:

Year = YEAR ( 'My Dates'[Date] )

Create a relationship from the Dates table to the fact table.

Create some Measures m(in the fact table:

Total Quantity = SUM( [Sale Quantity] )

Total Sales Amount = SUM( [Sale Amount] )

Sale Price = DIVIDE ( [Total Sales Amount], [Total Quantity], 0 )

Total Quantity LAST YEAR = CALCULATE ( [Total Quantity], SAMEPERIODLASTYEAR ('My Dates'[Date] ) )

Total Sales Amount LAST YEAR = CALCULATE...<I think you can figure this one out, yes?>

Sale Price LAST YEAR = DIVIDE ( <yeah, I think you got this one too! > )

Now set up a Table Visual and add 'My Dates'[Year], then add all the above measures.

To prove it works, look at, say [Total Quantity LAST YEAR] for 2022 and compare it to [Total Quantity] of 2021. It should exactly match.

Now for the climax, create a measure:

My Increase or Decrease = ([Sale Price] - [Sale Price LAST YEAR] ) * [Total Quantity]

Add that as maybe a card visual, and FILTER the data for THIS YEAR, or use a Relative Date Slicer.

Hope that helps.

[MikeUS]

Hi, thanks for the step-by-step guide - really appreciate!! 

made a screenshot of the real data and hope you can read that ? 

Problem: the total amount per year is not correct 

Correct is 5443 on line level

Total 6998 aggregate isnt correct

[ToddChitt]

Can you explain, in plain language (not DAX or pseudo-code) what your final measure is supposed to represent?

And please include things like data types and/or units. Is DATA 6 currency? Data 3 is what? Price per unit, averaged over the year?

[MikeUS]

Data 1 = Sales Quantity in Pieces

Date 2 = Sales Amount in Currency

Data 3 = Actual Sales Price in Currency

Data 4 = Last Year Sales Price in Currency

Data 5 = Actual Sales Price minus Sales Price last year in Currency to understand if Sales Price increased or decreased

Data 6 = depending on the sales price development (increase or decrease) I earn more or less money. To understand the magnitude of my lost or win (today) I multiply the sales price difference with the quantity I have sold.

Aim is do understand did I lose or make money overall as one item can be negative and I lose money but the portfolio overall should lead to more sales. In the example I actually lose 8400 USD (-8400)

[ToddChitt]

First, you need to create a custom COLUMN that does the [Quantity] x [Price] math:

Data 6 COLUMN = [Data 5] x [Data 1] (assuming both of these are columns too.)

Now create your measure as:

TOTAL of Data 6 = SUM ( [Data 6 Column] )

[MikeUS]

Only DATA 1 & DATA 2 are columns, Data 3 until Data 6 are measures. 

DATA 1 = DATA from Sales Report (= Total Sales Quantity)

DATA 2 = DATA from Sales Report (= Total Sales)

DATA 3 = Measure ( Sales Price YTD = DATA 2 / DATA 1)

DATA 4 = Measure (Sales Price Last YEAR = CALCULATE([Sales Price YTD],  DATEADD('Time Table'[Date], -1,YEAR))

DATA 5 = Measure (DATA 4 - DATA 3)

DATA 6 = Measure (DATA 5 x DATA 1

[ToddChitt]

What is your goal here? If you want [Total Last Year] then do something like this:

Last Year = CALCULATE ( [Total X], SAMEPERIODLASTYEAR ( 'Dates'[Date] )

Then you can add this:

YTD Last Year = TOTALYTD ( [Last Year], 'Dates'[Date] )

Seems you are using a YTD measure as the basis of your [Last Year] measure

[MikeUS]

calculating the YTD or Last Year total isnt the issue. 

my table contain multiple year / months, one item level the comparision of YTD and last year makes sense and the right difference between these two year get calculated. but on months level (total per months) it doesnt make sense anymore as. 

for example: DATA 6 - if you sum everything up in the month March 2023 you get -8400, PowerBI is calculating -11900 what is wrong 

[ToddChitt]

Ah. OK. Is it something like [Quantity] x [Price]? If so, you need to calculate that at the individual LINE level, the create a measure to aggregate the sum.

You cannot aggregate the individula columns first and then do the multiplication or division.

5 pieces x $10 each = $50

10 pieces x $20 each = $200

From this you cannot say:

Total of 15 pieces x total of $30 each = $450.

You have to do the SUM of $50 + $200 = $250

[MikeUS]

Correct! Line would be -5 (Data 5)  x 500 (Data 1) --> Result = Data 6  --> -2500

Aggregate should be Data 6 --> -8400 but right now i get -11900

How would that measure to aggregate the sum look like ?