Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculate rate for specific regions

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,

REGIONCOUNTRYREVENUE
EuropeGermany100
APACJapan100
LATAMBrazil100
EuropeAustria100
APACAustralia100
LATAMArgentina100
EuropeFrance100
APACSingapore100
LATAMMexico100
EuropeSpain100

 

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

APAC300.0030.00%
Europe400.0040.00%
LATAM300.0030.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

Austria100.0025.00%
France100.0025.00%
Germany100.0025.00%
Spain100.0025.00%

 

 

Thanks and sorry for silly question!

 

 

 

Need Help
Tips and Tricks
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi Anonymous ,

    Do you mean this?

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

     

     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

       

       

       

  • v-xuding-msft's avatar
    v-xuding-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Do you mean this?

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