Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Salesfile contains the fields Invoice Ref, Date, Week, Customer, Product & Quantity. (To keep things simple in this example the invoice line is unique for each row).
How can the invoice lines be listed in a Power BI Table (not Excel) just for products with more than 1 invoice in any week?
See example of Salesfile and the Desired Report.
Note the Radio product only had one invoice in week 5 but 2 in week 6
Many thanks in advance for sharing hints and tips.
Example Salesfiles:-
Product | Week | Customer | Quantity | Date | Invoice |
Car | 4 | John | 25 | 26-Jan-18 | 5894 |
Kettle | 2 | Mary | 16 | 11-Jan-18 | 9864 |
Laptop | 2 | Gill | 28 | 11-Jan-18 | 5678 |
Phone | 3 | Paul | 8 | 14-Jan-18 | 3256 |
Phone | 3 | Kath | 17 | 14-Jan-18 | 3256 |
Radio | 5 | John | 34 | 30-Jan-18 | 7654 |
Radio | 6 | Gill | 31 | 05-Feb-18 | 8786 |
Radio | 6 | Mary | 19 | 10-Feb-18 | 4568 |
TV | 1 | Mary | 28 | 01-Jan-18 | 1234 |
TV | 1 | Fred | 14 | 05-Jan-18 | 2267 |
Washing Machine | 3 | John | 19 | 16-Jan-18 | 1567 |
Desired report:-
Product | Week | Customer | Quantity | Date | Invoice |
Phone | 3 | Paul | 8 | 14-Jan-18 | 3256 |
Phone | 3 | Kath | 17 | 14-Jan-18 | 3256 |
Radio | 6 | Gill | 31 | 05-Feb-18 | 8786 |
Radio | 6 | Mary | 19 | 10-Feb-18 | 4568 |
TV | 1 | Mary | 28 | 01-Jan-18 | 1234 |
TV | 1 | Fred | 14 | 05-Jan-18 | 2267 |
Solved! Go to Solution.
Hi @Ramps
One way is to create a CALCULATED TABLE....
From the Modelling Tab >>> NEW TABLE
Table = VAR supportingtable = ADDCOLUMNS ( SalesFile, "Morethan1Invoice", CALCULATE ( COUNT ( SalesFile[Invoice] ), ALLEXCEPT ( SalesFile, SalesFile[Product], SalesFile[Week] ) ) ) RETURN FILTER ( supportingtable, [Morethan1Invoice] > 1 )
Hi @Ramps
One way is to create a CALCULATED TABLE....
From the Modelling Tab >>> NEW TABLE
Table = VAR supportingtable = ADDCOLUMNS ( SalesFile, "Morethan1Invoice", CALCULATE ( COUNT ( SalesFile[Invoice] ), ALLEXCEPT ( SalesFile, SalesFile[Product], SalesFile[Week] ) ) ) RETURN FILTER ( supportingtable, [Morethan1Invoice] > 1 )
Thank you Zubair_Muhammad for helping me twice this week with hints and tips.
I got your well explained solution to work OK, thank you, and I will try the 2nd suggestion later.
Or you can add a calculated column and use it to filter the table
Column = CALCULATE ( COUNT ( SalesFile[Invoice] ), ALLEXCEPT ( SalesFile, SalesFile[Product], SalesFile[Week] ) )
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |