locked
% of Parent Total whit Dax Expressions RRS feed

  • Question

  • Hi,

    While I've been trying to create a Dax expression that allows me to get the same result I get when I apply the option PivotTable "Show values as"> "% of Parent Total..." the question is I need to create several metrics different whole or base 100%, here an example of how are my data and the column as it should be correct.

    I appreciate your help to find out if you can really make or definitely should seek another solution

    Friday, February 26, 2016 10:03 PM

Answers

All replies

  • Excel 2010 with free PowerPivot Add-In.
    Compatible with Office 2013/2016 Pro Plus.
    With "Show Values As" and with DAX.
    My own example, didn't see yours.
    http://www.mediafire.com/download/1m3f9qqhe40pgmn/02_28_16.xlsx

    Sunday, February 28, 2016 11:56 PM
  • Hi Edwin,

    It's hard for us to understand your issue based on your description. Generally, we use the DAX expression below to calculate the percent of total.
    =Sum(Fact[Sales])/Calculate(Fact[Sales],All(Fact))
    Please refer to the link below to see the details about how to calculate the percent of total.
    http://samuelvanga.com/2015/09/29/calculate-percentage-share-in-power-bi-dax/
    https://blog.westmonroepartners.com/favorite-quick-easy-dax-calculate-percent-of-total-scenario/

    If this is not what you want, please elaborate the issue and provide us some sample data and screenshot about it, so that we can make further analysis.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, February 29, 2016 6:54 AM
  • Hello, thank you for your response
    Really, it is that the question was not very clear, but at the time of publishing, the site does not let me attach the image, which had been created in order to more clearly explain my problem.

    See the links you sent me and are very good, thank you again, but still do not understand how it should do in the filters so that when Anido several levels, the value is calculated at the level of total main lines.

    Even today the site will not let me post pictures, I do not know if you can share an email address where you can send the sample so you can check my problem much better

    Regards,

    Edwin

    Tuesday, March 1, 2016 6:56 PM
  • Pictures and images would be of no help at all.
    Upload your *.xlsx file to any file sharing site and post the link as text.
    No email, that would remove it from community participation.

    Tuesday, March 1, 2016 7:43 PM
  • Hi Herbert,

    I found a issue about your example, if in the area rows only left (Region, Rep) the value of "Rep" is correct, that is the sum of the percentages given 100% from the field "Region"; but to add to rows other ejm value (Region, Rep, Item), the sum of the latter level "Item" does not give 100% from the field "Region"

    the correct values should be as shown in column "Pct_Reg"

    Region Rep Item Sales Pct_Reg
    SW      $ 10,215 100%
      BYG    $   2,531 25%
        HA882  $      615 6%
        JA221  $      824 8%
        RJ061  $      528 5%
        XY200  $      523 5%
        ZZ750  $        41 0%
      ELL    $   2,222 22%
        AB101  $     ,356 13%
        JA221  $      866 8%
      NEO    $   5,462 53%
        AB101  $   1,454 14%
        HA882  $    1,106 11%
        LL002  $    1,029 10%
        PY552  $       667 7%
        RJ061  $     1,206 12%

    Example him out by "% of parent total," but I need to generate DAX, because I must create barias expressions with different base "100%" You could?

    Gracias

    Tuesday, March 1, 2016 9:01 PM
  • Hola, this is link

    https://www.dropbox.com/s/xj0i35le4kmopkl/%25%20of%20Parent%20Total.xlsb?dl=0

    Gracias

    Tuesday, March 1, 2016 9:52 PM
  • Excel 2013/2016 Pro Plus.
    Edited your file to make it look more like a real Business App.
    There are oodles of PivotTable configurations and
    each Calculated Field might need to be tweaked to reflect changes.
    Thus any changes to my examples will need to be reviewed by me and
    I might have to rewrite the PT Model possibly from the ground up
    to accommodate the changes.
    http://www.mediafire.com/download/27b0773975pljr3/03_01_16.xlsx

    Wednesday, March 2, 2016 12:37 AM
  • Herbert Hello, thanks for your response.
    Really the example you sent me the first time is fine, no need to change the table of information, the idea is to know about the same as I do to make even though add more levels to the rows they always continue calculating versus the base 100%.

    Not know how you can attach files, but it made me know how, then you attached the same file that you sent me for about the same see the values that you mark in green, these would be the correct values need to show but DAX expressions.

    https://www.dropbox.com/s/johrh0cmzjsznlw/02_28_16.xlsx?dl=0

    Gracias

    Wednesday, March 2, 2016 3:27 PM
  • Excel 2013/2016 Pro Plus.
    If you want to add Table1[Item] to the Row Labels,
    just add Table1[Item] to the ALL() filters.
    http://www.mediafire.com/download/l2s95kmby1921sn/02_28_16a.xlsx

    Any book on PowerPivot will explain this.

    Wednesday, March 2, 2016 5:56 PM
  • Excellent Herbert!, this is exactly what I needed, I am new to this topic DAX and this is really very useful.
    Again thank you for your time and valuable assistance.

    Regard,

    Edwin

    Wednesday, March 2, 2016 7:39 PM