Percentage delivered by country by day gives 100%

[DaveBonehill]

Hi All,

First time posting, hope someone can help me.

I am creating a Proof of Delivery (POD) report based on parcels being delivered to multiple countries in Europe.

I have the following data sources:

DHL Data - this is the master data list from the carrier and includes the Campaign Reference, Store Number, Delivery Date, Count of Parcels (along with others)

Store Lookup - Store number, country

Despatch Tracker - Campaign Reference, Despatch Date

Calendar Table - Cos' you have to have a calendar table, its the law.

When I have one campaigns worth of data, all works fine. I can work out the percentage of parcels that drop on each day following despatch (by country, as weekdays only)

The cardinality between DHL Data and Store Lookup is 1:1.

The problems start when I add in more campaigns, I have to change the cardinality to 1:Many which then makes all of the despatches 100% - it delivered 2 parcels of 2 on the date specified, rather than 2 parcels of the total of 23.

If I need to post more info then please let me know.

Thanks

Dave

[amitchandak]

@DaveBonehill , Can you share sample data and sample output in table format?

[DaveBonehill]

Hi,

cannot share all as there is sensitive data in but here are some downloads of what i believe is relevent.

Store Lookup

Store Number	Store Country
27	PORTUGAL
32	PORTUGAL
50	SPAIN
53	PORTUGAL
66	GERMANY
67	NETHERLANDS
71	NETHERLANDS
78	NETHERLANDS
93	GERMANY
111	NETHERLANDS
126	IRELAND
132	NETHERLANDS
157	FRANCE
219	NETHERLANDS
224	BELGIUM
232	NETHERLANDS
262	NETHERLANDS
278	GERMANY
287	PORTUGAL
316	PORTUGAL
325	NETHERLANDS

DHL Data

Store Number	Last Event date.1	Packs
1026	21/08/2020 00:00	1
1031	20/08/2020 00:00	1
1059	20/08/2020 00:00	1
1065	20/08/2020 00:00	1
1072	20/08/2020 00:00	1
1076	20/08/2020 00:00	1
1077	20/08/2020 00:00	1
1102	20/08/2020 00:00	1
1112	20/08/2020 00:00	1
1133	20/08/2020 00:00	1
1144	20/08/2020 00:00	1
1146	20/08/2020 00:00	1
1147	20/08/2020 00:00	1
1150	20/08/2020 00:00	1
1151	21/08/2020 00:00	1
1160	20/08/2020 00:00	1
1163	21/08/2020 00:00	1
1187	21/08/2020 00:00	1
1200	20/08/2020 00:00	1
1203	20/08/2020 00:00	1
1222	20/08/2020 00:00	1
1248	20/08/2020 00:00	1
126	20/08/2020 00:00	1
1264	20/08/2020 00:00	1
1274	20/08/2020 00:00	1
1336	20/08/2020 00:00	1
1352	20/08/2020 00:00	1
1367	20/08/2020 00:00	1
1401	20/08/2020 00:00	1
1413	20/08/2020 00:00	1
1423	20/08/2020 00:00	1

Output looks like this but it needs ti show percentage of overall count (by country)

[Pragati11]

HI @DaveBonehill ,

Can you share calculation of "Percentage Delivered" metric?

Thanks,

Pragati

[DaveBonehill]

Hi Pragati,

Percentage Delivered =

DIVIDE([Packs Delivered],SUM(DHL[Packs]))

Packs Delivered =

var _packs=CALCULATE(COUNTROWS(DHL), DHL[Status] = "Shipment Delivered")

return

IF(ISBLANK(_packs),0,_packs)

thanks

dave

[DaveBonehill]

I have updated the percentage delivered measure to the following which has meant i can change the cardinality to 1:Many. This has allowed me to add more data into the table.

Percentage Delivered =

DIVIDE(CALCULATE(COUNTROWS(DHL), DHL[Status] = "Shipment Delivered"),CALCULATE(SUM(DHL[Packs]),ALLEXCEPT(DHL,DHL[Last Event date.1])))

the problem now is that the main visual works until i select a campaign or country. The calculation is still being performed on the entire pack count rather than the selected filters.

With nothing selected i get this (which is what i expect):

When i select a campaign or country i get this:

[v-alq-msft · Accepted solution]

Hello, @DaveBonehill

Please try the next step to see if it works.

Percentage Delivered =
DIVIDE (
    CALCULATE ( COUNTROWS ( DHL ), DHL[Status] = "Shipment Delivered" ),
    CALCULATE ( SUM ( DHL[Packs] ), ALLSELECTED ( DHL ) )
)

Best regards

Allan

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[DaveBonehill]

Many thanks Allan.