Calculate rate for specific regions

[Anonymous]

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!

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Do you mean this?

Measure =
VAR region =
    CALCULATE ( SUM ( 'Table'[REVENUE] ), ALLEXCEPT ( 'Table', 'Table'[REGION] ) )
RETURN
    DIVIDE ( SUM ( 'Table'[REVENUE] ), region )

 

[parry2k]

@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!

[Anonymous]

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?