locked
Rollup percentages to groups with DAX? RRS feed

  • Question

  • I am using PowerPivot to feed Power View in order to visualize revenue versus target and am running into issues when going into more granular level and rolling up percentages.

    When show by month and individual country it is fine, but when adding up to regions using slicers, and/or not splitting by month, it adds the percentages up. Also because there are obviously different ratios between the various countries revenue in a region doing an average of percentage doesn't work. 

    I'm pretty sure I can do this with DAX ROLLUP, but I just cannot figure it out, assuming there are further steps that needs taken before using this function.

    I am using countries, regions, Month_Year, Target and Actual Revenue, eg with fictive revenue figures:

    Country Region Month_Year Target_revenue Actual_Revenue RevenueVtgt%
    Spain Europe 01/06/2014 100000 80000 80%
    Italy Europe 01/06/2014 80000 78000 98%
    Spain Europe 01/07/2014 90000 90000 100%
    Italy Europe 01/07/2014 75000 70000 93%
    Canada N_America 01/06/2014 80000 75000 94%
    US N_America 01/06/2014 130000 130000 100%
    Canada N_America 01/07/2014 70000 69000 99%
    US N_America 01/07/2014 140000 139000 99%

    Can anyone point me in the right direction as to which steps needs to be taken in order to rollup to regions?

    Sunday, September 14, 2014 12:53 PM

Answers

  • sandstp, 

    You might want to check out this article. 

    Clever Hierarchy Handling in DAX

    Hope this helps. 

    Reeves


    Denver, CO

    • Proposed as answer by Michael Amadi Monday, September 15, 2014 6:10 PM
    • Marked as answer by Elvis Long Friday, September 26, 2014 2:56 AM
    Monday, September 15, 2014 6:20 AM
  • Percentages in multidimensional and tabular should be calculated as SUM(Dividend) / SUM(Divisor)

    in your case try this:

    Revenue %:=DIVIDE(SUM('Facts'[Actual_Revenue]), SUM('Facts'[Target_revenue))

    this way you get an weighted average across any possible aggregations which works on all subtotals

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Thursday, September 18, 2014 8:47 PM
    • Marked as answer by Elvis Long Friday, September 26, 2014 2:56 AM
    Thursday, September 18, 2014 2:27 PM
    Answerer

All replies

  • sandstp, 

    You might want to check out this article. 

    Clever Hierarchy Handling in DAX

    Hope this helps. 

    Reeves


    Denver, CO

    • Proposed as answer by Michael Amadi Monday, September 15, 2014 6:10 PM
    • Marked as answer by Elvis Long Friday, September 26, 2014 2:56 AM
    Monday, September 15, 2014 6:20 AM
  • Percentages in multidimensional and tabular should be calculated as SUM(Dividend) / SUM(Divisor)

    in your case try this:

    Revenue %:=DIVIDE(SUM('Facts'[Actual_Revenue]), SUM('Facts'[Target_revenue))

    this way you get an weighted average across any possible aggregations which works on all subtotals

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Thursday, September 18, 2014 8:47 PM
    • Marked as answer by Elvis Long Friday, September 26, 2014 2:56 AM
    Thursday, September 18, 2014 2:27 PM
    Answerer