Calculate Z Score

[Anonymous]

Hello

PLease could you advice?

I got a dataset lets say there are 3 slicers

Product Name    Product Category, Client Name

I got a sales fact table, which has key of product + client as foreign key and there's relationship btween product name and product category and client and sales fact and so on

My problem is I know the caculation I want to get but I dont know how to dax it correclty. I ve done it but it didnt give me the right output

so on my product dim table, i insert new column called Sales total (which is a measure from fact sales -> sum(sales)

ProductName    Sales Total

Soap                  200

Toothpaste        100

Toothbrush        500

so my formula will be (200- average(sales total)) / std.s(sales total) -> assuming that 200 refers to the first row.

Now bear in mind these value will change depending on the slicers.

problem is that how do I translate this requirement into right dax measures plz help?

I think where go wrong is working out the average in my Filter context.

Currently I have calculate(average[sales total],all(product))

[v-huizhn-msft]

Hi @Anonymous,

In your sales fact table, if there is unique ProductName.  If it is, you just create a measure  to get the sales total for each ProductName.  And the result of average(sales total) is equal to average(sales), please review the following solution.

The following picture shows the source data.

 
Create a sale total measure using the formula.

Sales Total = CALCULATE(SUM(Test1[Sales]),ALLEXCEPT(Test1,Test1[ProductName]))

Then you can transfer the requirement of “ (200- average(sales total)) / std.s(sales total) -> assuming that 200 refers to the first row” to measure using the formula below.

Expected result = (CALCULATE(SUM(Test1[Sales]),ALLEXCEPT(Test1,Test1[ProductName]))-CALCULATE(AVERAGE(Test1[Sales]),ALL(Test1)))/CALCULATE(STDEV.S(Test1[Sales]),ALL(Test1))

Create a table, select the ProductName, “SalesTotal” measure and “Expected result” as value level, it will return the expected result.

 
In addition, if the value of ProductName column is not unique, you can create distinct table like the sample data table above using the formula below, then use the new table and solution above to get result.

New Table = SUMMARIZE(Test2,Test2[ProductName],"Sales",SUM(Test2[Sales]))

 

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[Anonymous]

Hi there @v-huizhn-msft thanks for your reply. I think my formula is more or less like yours. I found that my problem was actually trying to Summarise the table

Apologies I dont have a screenshot but I will try to explain it as details as I can.

Sales fact table

salesid

productid

contactid

product table

product id

product sub category

product id

product sub cat id

customer table

companyid

contactid

supplier table

supplierid

I create a new table which summarise the Sales total by product.

i want many slicers so I can slice them by "all" dimensions as above but as you could see there are some that have many to many relationships e.g one supplier can sell my products and one products may be sold by multiple suppliers vice versa.

My Summarise table include Sum of Sales but when I applied above slicers I seem to lose that granularity because in Sales table there are link to suppliers or company indrectly.

Hope this make sense? I start to feel like if I can get the summarise table right first then my calculate will be correct but I will also give your solution a go tonight as well. Thanks so much for replying.

[v-huizhn-msft]

Hi @Anonymous,

The easiest way is to summarize get distinct table. If you have any issue, please feel free to ask.

Best Regards,
Angelia