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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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

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
Super User
Super User

@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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors