Forum Discussion

angsoka's avatar
angsoka
Frequent Visitor
3 years ago
Solved

Percentage based on specific row

Dear all,

I want to create a measure on powerpivot which calculate the export percentage on specific rows. Below is the part of the data. ALL-ALL means that it is the total export of every country.

id CountryCountryStatusid CommodityCommodityExport
1301IndonesiaState785Motorcycle10
1301IndonesiaState781Car20
1302ThailandState785Motorcycle30
1302ThailandState781Car40
9101ALL - ALLArea785Motorcycle100
9101ALL - ALLArea781Car200
9111ALL - ASIAArea785Motorcycle50
9111ALL - ASIAArea781Car80

 

I want to have a pivot table filtered by Commodity (Car or Motorcycle), Status (State or Area), and Area (ALL-ALL or ALL-ASIA)

CommodityCar
StatusState
CountryALL-ALL
  
CountryExport Share
Indonesia=20/200
Thailand=40/200


I have tried to create a measure below, but it doesn't work.

Export Share :=
DIVIDE(
     SUM('Table'[Export]),
     CALCULATE(
          SUM('Table'[Export])

          FILTER(Table,Table[Commodity] = "Car" && Table[Status] = "State" && Table[Country] = "ALL-ALL"))


Any help is appreciated!

angsoka

 

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    3 years ago

    angsoka 

    How will you select ALL-ALL in the Country slicer and expect the table to show Indonesia and Thailand?

    If you select ALL-ALL, the table will show ALL-ALL. You'll need to change the structure of your table

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

  • angsoka's avatar
    angsoka
    Frequent Visitor
    3 years ago

    Thank you AlB 

    In the full dataset, the column "COUNTRY" will only contain the country's name. Which then "ALL-ALL" will be just the total of every country. In that case, we don't need "ALL-ALL", "ALL-ASIA", "ALL-EUROPE".
    However, it will make the size of the database will be very huge given that the commodity is at least 300 products. 

    Here, the filter Country : ALL-ALL will only dictate the denominator. 
    ALL-ALL >> divide by 200
    ALL-ASIA >> divide by 80 

    While the filter "Status - State", will limit the name of the country and not "ALL-ALL", "ALL-ASIA", etc. 

    Best
    angsoka