Forum Discussion
Vehicles with zero utilisation
I have a register of all assets that is linked to a transaction table which includes number of days on hire if relevant to transaction type. This is linked by the asset number. How do i extract assets that do not have a transaction for sales &/or number of days hired in a month?
Hi KleptoKenny
You can create the following measure, then add it to a visual as a filter and make it to show items when value is 0. In the visual, use [EQUIP_CODE] from 'Asset Table'.
No. of Sales = COUNTROWS(FILTER('Transaction table','Transaction table'[Trans Type]="Sales"))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
4 Replies
KleptoKenny Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Asset Table
EQUIP_CODE GROUP DC+GC NAV21821 STEL 1STEL NAV21822 STEL 1STEL NAV21847 FORD 1FORD NAV21881 FORD 1FORD NAV21889 FORD 1FORD NAV21892 FORD 1FORD NAV21924 IVHE 1IVHE NAV21933 FORD 1FORD NAV22085 STEL 1STEL NAV22090 STEL 1STEL NAV22094 STEL 1STEL NAV22236 FORD 1FORD NAV2648 FORD 1FORD NAV4122 IVHE 1IVHE NAV4816 IVHE 1IVHE NAV4817 IVHE 1IVHE Transaction table
EQUIPMENT_CODE Trans Type HPT_F_TRAN_VAL_BC HIRE_START_DATE HIRE_END_DATE HPT_DAYS NAV21821 Depre £183.00 - NAV21821 Sales £200.00 01/01/2023 01/02/2023 31.00 NAV21822 Depre £182.00 - NAV21822 Sales £112.50 24/01/2023 27/01/2023 3.00 NAV21847 Depre £212.00 - NAV21881 Depre £267.00 - NAV21889 Depre £342.00 - NAV21889 Sales £495.00 16/01/2023 25/01/2023 9.00 NAV21892 Depre £292.00 - NAV21892 Sales £330.00 23/01/2023 22/02/2023 31.00 NAV21924 Depre £875.00 - NAV21924 Sales £550.00 16/01/2023 23/01/2023 7.00 NAV21924 Sales £550.00 09/01/2023 16/01/2023 8.00 NAV21933 Depre £348.00 - NAV22085 Depre £196.00 - NAV22085 Repair £202.78 - NAV22085 Sales -£64.29 10/01/2023 13/01/2023 - 4.00 NAV22085 Sales £225.00 06/01/2023 13/01/2023 7.00 On the asset table i have a list of assets that may not have a sales transaction. I would like to be able to apply a filter to select these so i know which assets do not have any sales revenue.
Hope this helps - sorry first time on this forum 🙂
Hi KleptoKenny
You can create the following measure, then add it to a visual as a filter and make it to show items when value is 0. In the visual, use [EQUIP_CODE] from 'Asset Table'.
No. of Sales = COUNTROWS(FILTER('Transaction table','Transaction table'[Trans Type]="Sales"))Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
