Percentage based on specific row

[angsoka]

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 Country	Country	Status	id Commodity	Commodity	Export
1301	Indonesia	State	785	Motorcycle	10
1301	Indonesia	State	781	Car	20
1302	Thailand	State	785	Motorcycle	30
1302	Thailand	State	781	Car	40
9101	ALL - ALL	Area	785	Motorcycle	100
9101	ALL - ALL	Area	781	Car	200
9111	ALL - ASIA	Area	785	Motorcycle	50
9111	ALL - ASIA	Area	781	Car	80

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

Commodity	Car
Status	State
Country	ALL-ALL
Country	Export 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

[angsoka]

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

[Anonymous · Accepted solution]

HI @angsoka,

I'd like to suggest you take a look at the following links to know the hierarchy level and use selectedvalue and switch function to achieve dynamic calculations based on current value:

Clever Hierarchy Handling in DAX - SQLBI

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

Regards,

Xiaoxin Sheng

[AlB]

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