Need help with Calculating a percentage

[ssraju]

Hi All, Here is the sample data i have and i would need help with calculating the percentage of Not delivered to display in a matrix.

Month_Year	Delivery type	Country	Shipment to	Status	Value
Jan_2020	Express	AU	International	Not Delivered	2
Jan_2020	Express	AU	International	Total Parcels	5
Jan_2020	Express	AU	Local	Not Delivered	0
Jan_2020	Express	AU	Local	Total Parcels	4
Jan_2020	Standard	AU	International	Not Delivered	3
Jan_2020`	Standard	AU	International	Total Parcels	5
Jan_2020`	Standard	AU	Local	Not Delivered	1
Jan_2020`	Standard	AU	Local	Total Parcels	6
Jan_2020	Express	Singapore	International	Not Delivered	3
Jan_2020	Express	Singapore	International	Total Parcels	9
Jan_2020	Express	Singapore	Local	Not Delivered	3
Jan_2020	Express	Singapore	Local	Total Parcels	14
Jan_2020	Standard	Singapore	International	Not Delivered	0
Jan_2020`	Standard	Singapore	International	Total Parcels	7
Jan_2020`	Standard	Singapore	Local	Not Delivered	2
Jan_2020`	Standard	Singapore	Local	Total Parcels	6

I would like to display the matrix as below:

	Total parcels	Not Delivered	% Not delivered
Express
AU	9	2	2/ (20
Singapore	23	6	6/ 36
Standard
AU	11	4	4/ 20
Singapore	13	2	2/ 36

I need to calculate the % delivered for each country.

% Not delivered =  Not Delivered / Total parcels of that country only

for example: Country AU has total of 20

                     Country Singapore has total of 36.

[parry2k · Accepted solution]

@ssraju add following measure and you will be good to go

Sum Parcel = SUM ( Parcel[Value] )

Total Parcel = CALCULATE ( [Sum Parcel], Parcel[Status] = "Total Parcels" )

Not Delivered = CALCULATE ( [Sum Parcel], Parcel[Status] = "Not Delivered" )

Total Country Parcel = CALCULATE ( [Total Parcel], ALLEXCEPT ( Parcel, Parcel[Country] ) ) 

% Not Delivered = DIVIDE ( [Not Delivered], [Total Country Parcel] )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@ssraju to visualize, use matrix visual,

- add Deliver Type/Country on rows

- add [Total Parcel], [Not Delivered] and [% Not Delivered] on values

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@ssraju add following measure and you will be good to go

Sum Parcel = SUM ( Parcel[Value] )

Total Parcel = CALCULATE ( [Sum Parcel], Parcel[Status] = "Total Parcels" )

Not Delivered = CALCULATE ( [Sum Parcel], Parcel[Status] = "Not Delivered" )

Total Country Parcel = CALCULATE ( [Total Parcel], ALLEXCEPT ( Parcel, Parcel[Country] ) ) 

% Not Delivered = DIVIDE ( [Not Delivered], [Total Country Parcel] )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡