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.
I want to create a mesure in Power Bi that counts if trucks are deployed and if so, sum the amounts for that day and can't succeed in it.
For our logistics I want to count if a truck has been deployed per day. The problem is that a truck is deployed more than once a day. The table below is my data and I want to achieve the results in table 2.
Date | Truck_name | Deployment | Amount |
10-4-2017 | N100 | 1 | 1200 |
10-4-2017 | N100 | 1 | 700 |
10-4-2017 | N101 | 1 | 1100 |
10-4-2017 | N102 | 1 | 1000 |
10-4-2017 | N104 | 1 | 900 |
11-4-2017 | N101 | 1 | 1000 |
11-4-2017 | N102 | 1 | 1100 |
11-4-2017 | N101 | 1 | 1100 |
|
| 8 | 8100 |
Date | Truck | Deployment | Deployed on day | Amount |
10-4-2017 | N100 | 2 | 1 | 1900 |
10-4-2017 | N101 | 1 | 1 | 1100 |
10-4-2017 | N102 | 1 | 1 | 1000 |
10-4-2017 | N104 | 1 | 1 | 900 |
11-4-2017 | N101 | 2 | 1 | 2100 |
11-4-2017 | N102 | 1 | 1 | 1100 |
|
| 8 | 6 | 8100 |
Any help is welcome.
Edwin
Solved! Go to Solution.
Sorry to confuse you, the CountX is for new column created with the Count. In my sample i put the same name but is referencing the new column.
Unieke inzet = COUNTX ( SUMMARIZE ( NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet;
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[order_date];
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[truck_code]; "Deployment"; COUNT(
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[Inzet]));
[Deployment])
)
HI, use this measure:
Deployed = COUNTX ( SUMMARIZE ( Table1, Table1[Date], Table1[Truck_name], "Deployment", COUNT ( Table1[Deployment] ) ), [Deployment] )
Deployed = COUNTX ( SUMMARIZE ( Table1, Table1[Date], Table1[Truck_name], "Deployment", COUNT ( Table1[Deployment] ) ), [Deployment] )
This is the actual formula below, but the column's are in my native language.
Table = "NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet"
Unieke inzet = COUNTX ( SUMMARIZE ( NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet;
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[order_date];
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[truck_code]; "Deployment"; COUNT(
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[Inzet]));
[Inzet])
)
The error I get is:
Column 'Inzet' cannot be found or may not be used in this expression
Sorry to confuse you, the CountX is for new column created with the Count. In my sample i put the same name but is referencing the new column.
Unieke inzet = COUNTX ( SUMMARIZE ( NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet;
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[order_date];
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[truck_code]; "Deployment"; COUNT(
NIJ_Inzet_Pompen_v2_20_inzet_omzet_afzet[Inzet]));
[Deployment])
)
Thanks Victor. I'm still learning and try to get my head around DAX.
But what is actually going on in the formulla?
You sum truck_code per order_date and store the results in a temp column "Deployment"
and then you count the number of "Inzet" per (temp created) "Deployment"?
could you explain exactly what the formula is doing. I would apriciate that a lot.
Thanx so far.
Edwin
There are several steps. First create the core Table 2 where there is 1 date per truck. Use the Query Editor to create the new table:
Table2 = SUMMARIZE(Table1, Table1[Date],Table1[Truck])
This should result in 1 record per date/truck.
Then you'll create measure for a DeployCount per truck per day.
A measure for a Sales Sum per truck per day.
These 2 measures can be added into your visual level of Table 2.
Not sure what is meant by the Deployment On Day......
Here is a partial solution
Deployed on Day = IF(HASONEFILTER([Truck_name]),IF([Deployments]>=1,1,0),COUNT(Trucks[Deployment]))
Just have to figure out the last section in red for getting the right count in the context of ALL.
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 |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |