Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm quite fresh to Power BI so sorry if this was alredy created but i have issue/question
lets assume i have data like: Regioons/Country/Revenue and want to calculate % of revenue for given region,
REGION | COUNTRY | REVENUE |
Europe | Germany | 100 |
APAC | Japan | 100 |
LATAM | Brazil | 100 |
Europe | Austria | 100 |
APAC | Australia | 100 |
LATAM | Argentina | 100 |
Europe | France | 100 |
APAC | Singapore | 100 |
LATAM | Mexico | 100 |
Europe | Spain | 100 |
I know that i can create measure that will sum all revenue and then "pivot" it so i can see totals, to make % for given region i can just use Calculate + ALL and after that divide "Total revenue" by Calulate/All" so i can have % for given region just like below,
REGION Revenue % Total
APAC | 300.00 | 30.00% |
Europe | 400.00 | 40.00% |
LATAM | 300.00 | 30.00% |
But i was wondering, what if i want to calulate only Europe and run % by coutry? - to take only revenue for Europe, i use calculate and than add filter = Europe, but what about total europe revenue cant use ALL because i dont want to comapre Europe revenue to total anymore, i would like too see reasult adding up tp 100% like below, i know that i can specify to calulate only for given countries but would be easier and fatser to have only 1 formula
Country, Revenue , % Total
Austria | 100.00 | 25.00% |
France | 100.00 | 25.00% |
Germany | 100.00 | 25.00% |
Spain | 100.00 | 25.00% |
Thanks and sorry for silly question!
Solved! Go to Solution.
Hi @Anonymous ,
Do you mean this?
Measure =
VAR region =
CALCULATE ( SUM ( 'Table'[REVENUE] ), ALLEXCEPT ( 'Table', 'Table'[REGION] ) )
RETURN
DIVIDE ( SUM ( 'Table'[REVENUE] ), region )
Hi @Anonymous ,
Do you mean this?
Measure =
VAR region =
CALCULATE ( SUM ( 'Table'[REVENUE] ), ALLEXCEPT ( 'Table', 'Table'[REGION] ) )
RETURN
DIVIDE ( SUM ( 'Table'[REVENUE] ), region )
@Anonymous you can get total EUROPE revenue by the following measure
Europe Revenue =
CALCULATE ( SUM ( Table[Revenue] ), Table[Region] = "Europe" )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi thanks for reply,
My issue with this is that i cant calulate rates for all countries by 1 measue,
Lets say i want to calulate rate for all Europe countries so what i need to do.
1. Calculate total Europe revenue like you did below,
Europe Revenue = CALCULATE (SUM( Table[Revenue]),Table[Region] = "Europe")
2. Calculate revenue for specified country
Spain Revenue = CALCULATE (SUM( Table[Revenue]),Table[Country] = "Spain")
3. Rate Spain = Spain Revenue/Europe Revenue - i want so this add up to 100%
Can i somehow achive rates for all countries by 1 measure?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |