Standard deviation across department materials

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

I am trying to do a stdev.S across actual material numbers rather than a period in a department

My formula is as follows:

=STDEVX.S(VALUES(Table1[Article]),[Average Pack Height])

It calculates but with no answers.

Hope anyone can help

 

 

 

 

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

You are getting a blank because STDEVX.S is only finding one row for each value, which always will return a blank. Here the issue is likely going to be model design

If you had a parent table, Articles, which had only one row for each article, you could create a calculated column in the Articles table with a formula like (assuming a relationship between the Articles table and Table1 on the Article column):

Article Standard Deviation = STDEVX.S ( RELATEDTABLE ( Table1 ), Table1[Average Pack Height] )

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

Hi Tom

 

Thanks for the reply, I understand what you are saying.. I think :).

I created a fact table and article dimension table like you said and I created a counter (to count across the counter)?

I also related it back to the fact table (counter column) still nothing.

Please see attached

Regards

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Please see attached workbook. Only articles 7 and 10 had more than 1 related value for height.

Attachments:

You must be logged in to view attached files.

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

Thanks Tom

Why is there no Stdev for BMC “A” same applies for the other bmc ,  the height is 10,12,14,16 = Stdev 2.6 ?

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

Lets say all the articles are unique in the fact table ( fixed it) why does the countrows not apply on the BMC ?

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

Hi Tom

I see what you are doing, I am trying this please see below.

Trying to get a stdev across all the articles in the BMC from the fact table.

Thanks for the help sofar, it sparked some ideas for other stuff i am working on

Attachments:

You must be logged in to view attached files.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

Please see attached workbook.

Standard deviation is calculated in two places: More efficiently in a new parent table for individual BMC values and, less efficiently for each repeating BMC value in the fact table. If you need to have a copy of the PMC value in the fact table, you could just read it from the parent table using the RELATED function.

Attachments:

You must be logged in to view attached files.

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

thanks Tom

I bought the dax patterns book, but for some reason i still cant make these connections on how to construct these difficult yet simple requests.

Maybe it just above my IQ level 🙂 my brain cant make these connections.

I appreciate your help.

[tomallanKeymaster Started: 0Replies: 417Total: 417]

There is a learning curve. Would your company pay for DAX instruction like the online Comprehensive Power Pivot Course?

[rtwheelerParticipant Started: 5Replies: 16Total: 21]

To be honest I am willing to pay it myself i have a few questions:

# do you keep the online videos for review for anytime view?

#do they show practical examples in business and the same question type that I pose?

#Cost is 350$ per person or can one person have access and the rest can view it using my credentials?

# I am scared its the same format as the books ive got but it doesn’t explain everything… such as summarize, tips and tricks and other  funny and weird scenarios

[tomallanKeymaster Started: 0Replies: 417Total: 417]

rt,

The fee for the course is access to the on-line videos by one person for one year. The videos are arranged in various modules that cover topics like the formula engine, the CALCULATE function, time intelligence, working with multiple table models, disconnected slicers (disconnected tables), filtering, cube formula reporting, X functions (iterators), optimization and productivity. There are also summaries, tips, tricks, tips and discussions of “funny and weird” scenarios, including how to avoid pitfalls.

In addition to the 20+ hours of videos, there are approximately 45 workbooks that accompany the video instruction. There is also email-type access to an assistant instructor (not a video), who can further clarify the course information.

Before working for Rob I took the online course and I still consider the time and money spent an excellent investment. From my point of view, I have taken a number of different courses on DAX and Power Pivot, and there are only two great ones: 1) Rob’s Comprehensive Power Pivot Course (self-paced video training) and 2) Marco Russo’s and Alberto Ferrari’s “Mastering DAX“.

I am glad I took Rob’s course first because I learned how to think in DAX and the Power Pivot environment (not a small feat): my ability to answer your forum questions is a confirmation of this skill. The Russo/Ferrari course went in more detail in covering the individual functions, but it did not focus on productivity of the Excel professional as Rob’s course does. To me the Russo/Ferrari course gave me an excellent handle on inner workings of DAX and built up my conceptual and debugging skills.

There is no magic wand that will give us DAX insight, but both of the courses combined (one after the other) took me far along the path toward DAX mastery.

Tom

[Author]

Posts