Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good morning All,
I am working together with a transaction tables in which i have the sales by client like below
Client | Date | Product | Sum |
Client 1 | 1/2/2010 | Product 1 | 10 |
Client 1 | 1/3/2010 | Product 2 | 30 |
Client 1 | 1/4/2010 | Product 2 | 30 |
Client 2 | 1/5/2010 | Product 2 | 30 |
Client 2 | 1/6/2010 | Product 2 | 30 |
Client 2 | 1/7/2010 | Product 2 | 30 |
Client 2 | 1/8/2010 | Product 2 | 30 |
Client 2 | 1/9/2010 | Product 2 | 30 |
Client 2 | 1/10/2010 | Product 1 | 10 |
Client 3 | 1/11/2010 | Product 2 | 30 |
Client 3 | 1/12/2010 | Product 2 | 30 |
Client 3 | 1/13/2010 | Product 2 | 30 |
Client 3 | 1/14/2010 | Product 2 | 30 |
Client 3 | 1/15/2010 | Product 3 | 10 |
I want to calculate the the average total purchase by clients in 2010.
when i dont filter by Product this is very straight forward.
Ther issue i have is when i filter by product.
If i filter Product = Product 1, i would like the average spend in the year of my clients who purchased Product 1
So far i am having an average spend of 10 equals to product 1 although it should 115.
Thanks for your help!
Cheers
Solved! Go to Solution.
Hi, @Vincem35
You may create a calculated table and modify the measure as below. The pbix file is attached in the end.
Calculated table:
Product = DISTINCT('Table'[Product])
Measure:
Avg =
var tab =
SUMMARIZE(
FILTER(
ALL('Table'),
YEAR([Date])=2010
),
'Table'[Client],
"Flag",
COUNTROWS(
FILTER(
'Table',
[Product] in DISTINCT('Product'[Product])
)
),
"Result",
SUM('Table'[Sum])
)
return
AVERAGEX(
FILTER(
tab,
[Flag]>0
),
[Result]
)
Then you use 'Product' column from 'Product' table to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Vincem35
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a measure as below.
Avg =
var tab =
SUMMARIZE(
FILTER(
ALLSELECTED('Table'),
YEAR([Date])=2010
),
'Table'[Client],
"Result",
SUM('Table'[Sum])
)
return
AVERAGEX(
tab,
[Result]
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Allan for your help
Unfortunately, this is not exactly what i am looking for
For instance, once I select Product 1 i should be able to get the total average spend for the clients who purchased Product 1: in that case the average amount for Clients 1 and 2 : 115$
I shouldnt get only the amount spent for Product 1 but for all the products.
I hope it clarifies
Thanks a lot
Hi, @Vincem35
You may create a calculated table and modify the measure as below. The pbix file is attached in the end.
Calculated table:
Product = DISTINCT('Table'[Product])
Measure:
Avg =
var tab =
SUMMARIZE(
FILTER(
ALL('Table'),
YEAR([Date])=2010
),
'Table'[Client],
"Flag",
COUNTROWS(
FILTER(
'Table',
[Product] in DISTINCT('Product'[Product])
)
),
"Result",
SUM('Table'[Sum])
)
return
AVERAGEX(
FILTER(
tab,
[Flag]>0
),
[Result]
)
Then you use 'Product' column from 'Product' table to filter the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot Allan,
it works well on my full transaction tables but what would be your advice if i would like to passs some filters such as Market... etc
I guess i need to change
ALL('Table'),
Thanks
Hi, @Vincem35
You may try replacing 'ALL('Table')' with 'ALLSELECTED('Table')' to see if it works.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Vincem35 , Create a year column in table
New column
Year = year(Table[Date])
And try a measure like
measure =
var _tab = summarize(table,table[client], table[Year], "_pur",[total purchase])
return
averageX(values(_tab[client]),_pur)
Thanks Amit
What would you suggest instead of grouping by year as i have various dynamic time period for this calculations: P12M, P3M etc...
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |