Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
Solved! Go to 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
Regards,
Xiaoxin Sheng
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
Regards,
Xiaoxin Sheng
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. |
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
8 |