topic Re: help to calculate sales for each product in DAX Commands and Tips

help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 13:44:54 GMT

Note : I dont want to total sales for all products , i want to see sales for each product ID so i can see them on any visual

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 17:55:24 GMT

Hello,

Create a simple measure like SUM(RentPerMonth), drag the measure into a column chart, and drag property id on the axis.

Regards,

Adrian

Re: help to calculate sales for each product

parry2k — Wed, 19 Jun 2019 17:56:06 GMT

Anonymous in your post what you refer to product?? It is not very clear?

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 18:07:55 GMT

By product I mean property

I want to see total sales and standard deviation for each unique property

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 18:09:50 GMT

And same for standard deviation?

Use it's dax formula ?

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 18:26:41 GMT

What do you think about this?

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 18:52:11 GMT

StandardDev = STDEV.P('fiverr_db monthly_mar_19'[RevenueUSD])

i used this is it fine??

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 19:00:02 GMT

I can not confirm that, because it produces a weird result in my data set;

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 19:25:10 GMT

same here confused...

and standard deviation formula is way complex

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 19:34:00 GMT

I'm glad I could help

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 19:35:15 GMT

so what you suggest ? need your suggestion

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 19:47:29 GMT

Here you go;

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 19:50:13 GMT

I hope this is what your after:

Stdev (In Property) = CALCULATE(STDEV.S(Table1[Value]),ALLEXCEPT('Table1',Table1[Property]))
Avg (In Property) = CALCULATE(AVERAGE('Table1'[Value]),ALLEXCEPT('Table1',Table1[Property]))

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 19:52:21 GMT

can you please share formulas for delta AND squared delta and all calculation you did :)

i want to try with my data :)

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 19:55:12 GMT

Total Rent =

SUM('Property'[Rent])

Average Rent =

CALCULATE(AVERAGE('Property'[Rent]),ALL('Property'))

Delta =

[Total Rent]-[Average Rent]

Squared Delta =

[Delta]*[Delta]

St Dev =

SQRT(DIVIDE([Squared Delta],CALCULATE(COUNTROWS('Property'),ALL('Property'))-1))

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 20:06:25 GMT

just a random thought average of rent will be giving average of all rents for all property ids. right?

its not showing average rent for each property am i right?

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 20:14:05 GMT

That's correct. As you can see in the screenshots the average is equal on all rows.

If you want to use the average of a property you can change the measure into;

And also change the St Dev measure into;

SQRT(DIVIDE([Squared Delta],CALCULATE(COUNTROWS('Property'),ALLEXCEPT('Property','Property'[Property]))-1))

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 20:14:12 GMT

and*(Total rent ) sum will be changed in similar manner to show sum for each proerty ,rather then showing for all

Re: help to calculate sales for each product

Anonymous — Wed, 19 Jun 2019 20:17:06 GMT

and just for sake of understanding...what is the use of this allexcept and that subtraction(-1) in end

what that subtraction do..

Sorry for asking so many lame questions , Actually i am new to power bi and dax and i want to polish my skills in this field

Re: help to calculate sales for each product

avanderschilden — Wed, 19 Jun 2019 20:20:51 GMT

You send me the formula for standard deviation so I rebuild it in DAX for you so you can understand it. If you check the formula you posted, you will see -1 too.

You can find the full explanation of ALLEXCEPT here;

https://docs.microsoft.com/en-us/dax/allexcept-function-dax