Forum Discussion

ssraju's avatar
ssraju
Regular Visitor
5 years ago
Solved

Need help with Calculating a percentage

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.

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    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's avatar
    parry2k
    5 years ago

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

2 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

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