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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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