Forum Discussion
Filter solution
Hi!
From the example below i would like to filter sales in period 2020-01 where sales is more than 80. But i would likte to get all periods for companys that have sales of more than 80 in period 2020-01. Desired result should be to see Amazon sales in period 2020-01 and 2020-02 but not microsoft sales. is that possible and how to solve that?
Br
Arne
Example
Company Period Sales
Amazon 2020-01 90
Amazon 2020-02 70
Microsoft 2020-01 75
Microsoft 2020-02 100
Hi Arne ,
Try this measure:
Measure 2 =
VAR _company = SELECTCOLUMNS(FILTER(ALL('Table (3)'); 'Table (3)'[Sales] > 80 && 'Table (3)'[Period] = DATE(2020; 1;1)); "Company"; 'Table (3)'[Company])RETURN SUMX(FILTER('Table (3)'; 'Table (3)'[Company] IN _company); 'Table (3)'[Sales])Ricardo
3 Replies
- So maybe:
FILTER('Table',[Company] = "Amazon" && [Sales] > 80)Hi Greg!
In the example it would work but in my solution i,ve got hundreds of departments so i would need some solution that first filters out every department that has sales over a specific limit for a selected period and then i would like to see sales trends for these companys for the periods i choose. So it,s really a two step issue.
1. Find companys that qualify for selected sales level for the period i choose.
2. For companys that qualify for the first criteria show sales level for the period in first critera but also for other chosen periods.
Br Arne
Hi Arne ,
Try this measure:
Measure 2 =
VAR _company = SELECTCOLUMNS(FILTER(ALL('Table (3)'); 'Table (3)'[Sales] > 80 && 'Table (3)'[Period] = DATE(2020; 1;1)); "Company"; 'Table (3)'[Company])RETURN SUMX(FILTER('Table (3)'; 'Table (3)'[Company] IN _company); 'Table (3)'[Sales])Ricardo
