Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |