How to find if Individual Sales Amount is greater than Average Sales Amount or not in Tabular

[a4apple]

I am working on a SSAS Tabular cube Where I have fields like

"CustomerID" , "Rno", "No Of Trans", "Time", "Time Per Trans"(Calculated Column = DIVIDE([Time], [No Of Trans], 0))
1, 1, 100, 15000, 150

1, 2, 125, 22000, 176

1, 3, 150, 10000, 66.67

2, 1, 10, 5000, 500

These are the measures I am using

Sum Time Per Trans := SUMX('Sales', 'Sales'[Time Per Trans])

Avg Time per Trans :=  AVERAGEX('Sales', 'Sales'[Time Per Trans]) --> For Store 1 - 130.89, store 2 - 500, for all stores - > 223.17

I am trying to see if I can create a calculated column / table that can tell if each record in Sales Fact is greater than the Avg Time Per Trans.

I want to show a column at the end like 

"CustomerID" , "Rno", "No Of Trans", "Time", "Time Per Trans", "Higher than Avg"
1, 1, 100, 15000, 150, 1

1, 2, 125, 22000, 176, 1

1, 3, 150, 10000, 66.67, 0 

2, 1, 10, 5000, 500, 0

[Higher Than Average] column is nothing but if the "Time Per Trans" is greater than "Avg Time Per Trans" then 1 else 0. 

I am not sure if I have to create a Calculated Table or just a calculated column will work.

Thanks in advance

[v-yuezhe-msft]

Hi @a4apple,

Based on your description, I am not sure that if these fields ("CustomerID" , "Rno", "No Of Trans", "Time", "Time Per Trans") you post are in the Sales table. If not, please help to post sample data of Sales table, and we need to know the relationship between Sales table and the table that these fields ("CustomerID" , "Rno", "No Of Trans", "Time", "Time Per Trans") reside in.

If these fields are in the Sales table, please create Higher than Avg calculated column using the following formula, replace Table1 with ‘Sales’.

Higher than Avg = IF(Table1[Time Per Trans]>Table1[Avg Time per Trans],1,0)



Thanks,
Lydia Zhang