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
ste_ver
Frequent Visitor

Use set of customers as filter

Hi, is there a way to use a filtered list of data (for example : customers or products) to use as a filter for another calculation.

I have a typical dataset (fact, datetable, producttable and customertable).

What i would like to do first is to calculate the sales for all customers within a timerange (01/01/2017 - 30/06/2017) who bought for more than 10000 EUR . I already figured out how to do this, see Sales > 10000 formula. In my dataset, the result is the sales for 312 customers out of 1500 customers.

What i would like to do next is to calculate the sales of the previous year for this set of customers, in this case the sales from 01/01/2016 to 30/06/2016 for those 312 customers.

 

If i would split up the formule, i guess i first have to determine the filtered set of customers and use this set in the calculation.

unfortunately, this goes way over my head...

 

 

formula Sales > 10000

Sales > 10000=
    SUMX (
        FILTER (
            SUMMARIZE (
                'Fact';
                'Fact'[DimRelationId];
                "Total sum"SUM ( 'Fact'[Sales] )
            );
            [Total sum] > 10000
        );
        [Total sum]
    )

 

1 ACCEPTED SOLUTION

hey, thanks. Seems to work. Thanks for your asistance !!

i have to say, i've been working on this problem for a while now (i'm not that experienced in DAX as i would like to), and yesterday evening, i came up with a solution, however probably not as perfomant as yours. The amounts however match perfectly !!

( the column names are a bit different since these are from my production model ).

 

Omzet > Value J-1 =
VAR 
 ValueContext = max(Value[Value])
VAR
 TableFilter = FILTER( SUMMARIZE ( 'Fact' ; 'Fact'[DimRelatieId] ; "Total sum"; sum ( 'Fact'[NettSales] )  ) ; [Total sum] > ValueContext )
VAR
 TableColumn = SUMMARIZE( TableFilter ; 'Fact'[DimRelatieId] )
RETURN
 CALCULATE(
  [Sales J-1] ;
  FILTER (
   ALL ( 'Fact'[DimRelatieId] ); ( 'Fact'[DimRelatieId] ) IN TableColumn
       )
   )

View solution in original post

7 REPLIES 7
v-qiuyu-msft
Community Support
Community Support

Hi @ste_ver,

 

You can create a measure below: 

 

Measure = IF('Key Measures'[Sales]>MAX(KeyValue[Value]),CALCULATE([Sales],SAMEPERIODLASTYEAR('DimDate'[FullDateAlternateKey])),BLANK()) 

 

q10.PNG

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hi, thanks for the update. however, your calculation is correct on a row level, but the total is not calculated correctly.

the sum of the selected figures (red square) can't be the 3 million dollar you see in the row total.

 

total.png

nobody else a solution?

Hi @ste_ver,

 

Try this calculated field formula

 

=CALCULATE(SUM(FactInternetSales[SalesAmount]),SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey]),FILTER(VALUES(DimCustomer[CustomerKey]),[Sales of customer > value]>0))

This will give you the Sales of the 6 customers last during the same period last year.

 

Here's the file.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

hey, thanks. Seems to work. Thanks for your asistance !!

i have to say, i've been working on this problem for a while now (i'm not that experienced in DAX as i would like to), and yesterday evening, i came up with a solution, however probably not as perfomant as yours. The amounts however match perfectly !!

( the column names are a bit different since these are from my production model ).

 

Omzet > Value J-1 =
VAR 
 ValueContext = max(Value[Value])
VAR
 TableFilter = FILTER( SUMMARIZE ( 'Fact' ; 'Fact'[DimRelatieId] ; "Total sum"; sum ( 'Fact'[NettSales] )  ) ; [Total sum] > ValueContext )
VAR
 TableColumn = SUMMARIZE( TableFilter ; 'Fact'[DimRelatieId] )
RETURN
 CALCULATE(
  [Sales J-1] ;
  FILTER (
   ALL ( 'Fact'[DimRelatieId] ); ( 'Fact'[DimRelatieId] ) IN TableColumn
       )
   )

v-qiuyu-msft
Community Support
Community Support

Hi @ste_ver,

 

Please use some sample data to create a pbix file and share this pbix file with us. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.