help to calculate sales for each product

[Anonymous]

hi i am new to power bi and Dax , i need a little help i have a table with rents of different Properties .Structure of table is as follows :

Properties :

PropertyID |   RentPerMonth |   Month(date)  

----------------------------------
i want to see total revnue for each property ID

eg if  i have 2 properties eg 

prp1  |200 | July 2009
prp1  |300  | August 2009
prp1  |250  | september 2009
prp2  |215 | July 2009
prp2  |275  | August 2009
prp2 |288 | september 2009

now for each  property id  i want to see its total revnue generated ,secocdaly i want to see standard deviation against each property id

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


 

[Anonymous]

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]))

[parry2k]

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

[Anonymous]

By product I mean property

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

[avanderschilden]

Hello,

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

Regards,

Adrian

[Anonymous]

And same for standard deviation?

Use it's dax formula ?

[avanderschilden]

What do you think about this?

[Anonymous]

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

i used this is it fine??

[avanderschilden]

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

[Anonymous]

same here confused...

and standard deviation formula is way complex

[avanderschilden]

I'm glad I could help

[Anonymous]

so what you suggest ? need your suggestion

[avanderschilden]

Here you go;

[Anonymous]

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

i want to try with my data 🙂

[avanderschilden]

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))

[Anonymous]

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?

[avanderschilden]

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))

[Anonymous]

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

[avanderschilden · Accepted solution]

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

[Anonymous]

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