Forum Discussion
Calculate Z Score
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))
3 Replies
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,
AngeliaHi 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.
Hi Anonymous,
The easiest way is to summarize get distinct table. If you have any issue, please feel free to ask.Best Regards,
Angelia
