Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
)
Solved! Go to 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
)
)
Hi @ste_ver,
You can create a measure below:
Measure = IF('Key Measures'[Sales]>MAX(KeyValue[Value]),CALCULATE([Sales],SAMEPERIODLASTYEAR('DimDate'[FullDateAlternateKey])),BLANK())
Best Regards,
Qiuyun Yu
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.
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.
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
)
)
Hi @ste_ver,
Please use some sample data to create a pbix file and share this pbix file with us.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |