Deployments per day

[egrootoonk]

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

[Vvelarde]

@egrootoonk

HI, use this measure:

Deployed =
COUNTX (
    SUMMARIZE (
        Table1,
        Table1[Date],
        Table1[Truck_name],
        "Deployment", COUNT ( Table1[Deployment] )
    ),
    [Deployment]
)

 

[egrootoonk]

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

[Vvelarde · Accepted solution]

@egrootoonk

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])
) 

[egrootoonk]

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

[CahabaData]

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......

[Greg_Deckler]

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.