Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
angsoka
Frequent Visitor

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
angsoka
Frequent Visitor

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
Not applicable

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
Community Champion
Community Champion

@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

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.