Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Vincem35
Helper I
Helper I

Filter by all clients who purchased a product

Good morning All, 

 

I am working together with a transaction tables in which i have the sales by client like below

ClientDateProductSum
Client 11/2/2010Product 110
Client 11/3/2010Product 230
Client 11/4/2010Product 230
Client 21/5/2010Product 230
Client 21/6/2010Product 230
Client 21/7/2010Product 230
Client 21/8/2010Product 230
Client 21/9/2010Product 230
Client 21/10/2010Product 110
Client 31/11/2010Product 230
Client 31/12/2010Product 230
Client 31/13/2010Product 230
Client 31/14/2010Product 230
Client 31/15/2010Product 310

 

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

1 ACCEPTED 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.

d1.png

d2.png

d3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-alq-msft
Community Support
Community Support

Hi, @Vincem35 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

b1.png

 

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:

b2.png

b3.png

 

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.

d1.png

d2.png

d3.png

 

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.

amitchandak
Super User
Super User

@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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.