Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate Percentages In A Hierarchical Form In Power BI

I have a client/country sales matrix in power bi. How can i calculate de column 2019%

                                  2019       2019 %                         2020 ....

client 1                         30              33%=30/90

      Portugal              10             33% =10/30

      França                 20             66% = 20/30

Client 2                         60              66,6%= 60/90

       Espanha             30              50% = 30/60

       Bélgica               30              50% = 30/60

total                               90               100%

 

 

  • HI Anonymous 

    This measure should do the trick. I am assuming your data is coming from one table (if yes, please condider re-wiring into a dimensional model). 

     

    Percentage  =
    VAR countryInScope =
        NOT ISINSCOPE ( 'Table'[Country] )
    VAR result =
        DIVIDE (
            SUM ( 'Table'[Amount] ),
            IF (
                countryInScope,
                CALCULATE ( SUM ( 'Table'[Amount] ), REMOVEFILTERS () ),
                CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Client] ) )
            )
        )
    RETURN
        result

     

     
    You are welcome to create a separate measure for Sales Sales = SUM('Table'[Amount]and use it in the measure above instead of repeating the SUM func. 



    If I answered your question, please mark my post as a solution. Thank you!

1 Reply

  • HI Anonymous 

    This measure should do the trick. I am assuming your data is coming from one table (if yes, please condider re-wiring into a dimensional model). 

     

    Percentage  =
    VAR countryInScope =
        NOT ISINSCOPE ( 'Table'[Country] )
    VAR result =
        DIVIDE (
            SUM ( 'Table'[Amount] ),
            IF (
                countryInScope,
                CALCULATE ( SUM ( 'Table'[Amount] ), REMOVEFILTERS () ),
                CALCULATE ( SUM ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[Client] ) )
            )
        )
    RETURN
        result

     

     
    You are welcome to create a separate measure for Sales Sales = SUM('Table'[Amount]and use it in the measure above instead of repeating the SUM func. 



    If I answered your question, please mark my post as a solution. Thank you!