cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EdxStudent
New Member

Lab2 questions Sales Var% value

Please can somebody explain to me how Sales Var % figure is 2.74%

 

These are the values I got from the doing the  LAB 2

 

Total Sales = SUM(Sales[Revenue]) =5,127,755,449.65

LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date])) =4,877,019,942.08

Sales Var = [Total Sales] - [LY Sales] =250,735,507.08

Sales Var % = DIVIDE([Sales Var],[LY Sales]) =5.14%

 

Thanks

12 REPLIES 12
CodeWayfarer
Frequent Visitor

Re: Lab2 questions Sales Var% value

I have another value for "LY Sales", more than yours, then all 2 other figures (Sales Var & Sales Var&) also should be another.

All the figures were ok according to the lab.

Maybe you have some of filters ON?

Greenrjc
Frequent Visitor

Re: Lab2 questions Sales Var% value

I had the same problem, started from scratch and didn't use the downloaded Lab 2.

Got it right, remeber to change type on Var % to percentage.

alexei7 Continued Contributor
Continued Contributor

Re: Lab2 questions Sales Var% value

I just encountered the same problem

 

My formula was:

LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Sales'[Date]))

 

I get the correct figures when I correct it to:

LY Sales = CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))

 

not sure why the difference

wsnyder Advocate I
Advocate I

Re: Lab2 questions Sales Var% value

did exactly same as original poster and got same result... Totals fine, but %var bad.

 

Although I followed the instructions carefully, I had some quetions about them ... It did not make much sense to me - to use Total sales variances deriived with last years sales... seems like either the Total sales should be sliced by date, or calculated as a yearly value, then compared with last year....THen in the questions part of the lab it explicitly tells us NOT to filter or slice.

 

I have used DAX regularly in the past, and the instructions are simple to follow - but why is the answer wrong (like the first poster)

 

I Only started with the supplied powerbi code as a starting point and did not try from scratch.

wsnyder Advocate I
Advocate I

Re: Lab2 questions Sales Var% value

I now see the problem...

 

Switching between the Lab instructions and PowerBI, I skipped a step.

It is the step which creates a relationship between the Date table and the Sales table.

 

Add the relationship - now I get the correct answer..

 

(However I still do not see the business sense around a Variance calc which compares last year to total sales ) .. Unless in some future lab we will refine this and either filter or use a slicer...

 

DavidMoss Advocate V
Advocate V

Re: Lab2 questions Sales Var% value

I've just been testing the labs with some colleagues I am training in Power BI & DAX & Power Query M and the lab works fine for us.

 

Reading the replies above i suggest you do the following:

1: Ensure to use the new lab 2 .pbix file as per the instructions. Unfortunately you cannot continue to use the file form Lab 1 as there are differences in the data. (See lab1 geography table is called location in Lab2)

 

2: @EdxStudent @ alexei7 Ensure you create the date relationship between the (Sales[date]) and (Date[date]). This is essential as the Date table provides the full list of dates where as the sales table could be missing certain dates (if sales did not happen on a day). The continuous date series in the date table is essential for the function SAMEPERIODLASTYEAR.

 

3: @wsnyder the business logic & DAX logic is take a single measure then do the time intelligence on that measure. The measure [Sales Var] could be as you suggest sliced by dates see the function Dateadd for example. But PBI has made our life easier providing the higher level function like SAMEPERIODLASTYEAR and TOTALYTD etc…

 

Guys if that at all helps please click the Like button or better mark this as your answer resolved.

Or if not continue to ask and I will try to dig deeper into the subject.

varshneyaman
New Member

Re: Lab2 questions Sales Var% value

same values my side
josecorujeira
New Member

Re: Lab2 questions Sales Var% value

Ten en cuenta que al generar la medida de Sales Var % el formato debe de ser Percentage (en la parte superior sobre donde escribes DAX, al terminar la programación selecciona el formato)

 

lab2

 

Olia Helper III
Helper III

Re: Lab2 questions Sales Var% value

people, just a check... does everyone have exactly the SAME number for Revenue for each row?

I suspect that something is going wrong for me..

 

.image.png

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Kudoed Authors