locked
percentages RRS feed

  • Question

  • i have a matrix whose rows are site and some demographic (age, sex, country,...)

    columns are a set of year (2017,018,2019...)

    the values I want are count of people, and percentage of people meeting some criteria. I created a column on the table with is 0 or 1 depending on whether they meet the criteria. 

    I have the measure:

    total = sum('table'[people with criteria]) this works fine.

    I want the percentage of people, so I made a measure:

    %Persite = DIVIDE (
    'measuretbl'[total],

    CALCULATE ('measuretbl'[total], ALL ( 'dimSite' ) ) this also works fine

    however, when I expand and to see the demographic breakdown, the percentages are meaningless.

    how do I create a measure which will give me the correct percentages even when expanded.

    (percentage of each demographic relative to the total of the expanded site.

    so if the site is 10% of the total for all sites, by demographic I should see values totaling to 10%)

    this is what I am seeing. (top bolded row is for a site, rest of rows are expanded for a demographic)

    

    thanx



    ???

    Thursday, January 2, 2020 8:53 AM

Answers

  • I finally found it. the measure %persite must also remove all other filters so add more all('dim that may be filtered')

    %Persite = DIVIDE (
    'measuretbl'[total],

    CALCULATE ('measuretbl'[total], ALL ( 'dimSite' ),all('dim2' ),all(dim3))


    ???

    • Marked as answer by berli Wednesday, January 8, 2020 2:02 PM
    Wednesday, January 8, 2020 2:02 PM

All replies

  • Berli,

    It looks to me that this would be a problem better addressed in Power Pivot using DAX, not in your data model using Power Query.

    Some things on which I would suggest you take a different approach:

    1. Why do you have columns for years?  It would be much better if you had a (one) Date column for each record in your table.  If you data come to you with years as columns, unpivot them.  Let your calendar table do it's job.  Set a relation from the date column to your date table.  From there you can get year, quarter, month, or any other date related attribute.

    2. Using DAX you can calculate the Percentage people in a group of some criteria very easily.  Post this question in Power Pivot forum if you need assistance. 

    Just a bit of posting lessons learned I have found out:

    If you will post better information and even a copy of your workbook or BI file, it would help people help you better.  What I have learned is you have to be specific when asking for help.  Break down your problem into individual problems and ask for help on one specific issue at a time.  Post Power Query issues here and DAX issues in the Power Query forum.  Don't post your questions all over the place or if you do, be sure to tell everyone you also posted this question here, [and provide url].

    Hope this help...



    John Thomas

    Thursday, January 2, 2020 7:26 PM
  • the data model is fine. there is a data table with all the appropriate fields. 

    as I stated, the visual is a matrix where the user wants the statistics of several years separated by year. per site with a demographic breakdown.

    I posted on the power pivot form, but since this is a DAX question, I dont understand your answer and why this is not the right forum. this is one specific problem. I cannot post secure data 

    the visual is like 

    site                  2019           2018                 2017      2016

    usa   +         200  60%     150   55%           140

    uk     +        150  40%      130  45%            120

    total  +        350  100%    280   100%         260 ....

    ---------------------------------------------------

    when expanded I want, bit the percentages are worng

    site                2019                2018          2017      2016

     usa  male      120  30%         70    22%          

    usa female        80  26%        88    35%           

    uk male          75     42%       70     22%          

    uk female       75      42%       60     15%          ...

    total             200    100%   .  280 100%

    i Have the DAX for total = sum('table'[people with criteria]) this works fine.

    the Dax for the percentage works when not expanded, but fails when expanded

    details in my original post


    ???

    Sunday, January 5, 2020 1:54 PM
  • I finally found it. the measure %persite must also remove all other filters so add more all('dim that may be filtered')

    %Persite = DIVIDE (
    'measuretbl'[total],

    CALCULATE ('measuretbl'[total], ALL ( 'dimSite' ),all('dim2' ),all(dim3))


    ???

    • Marked as answer by berli Wednesday, January 8, 2020 2:02 PM
    Wednesday, January 8, 2020 2:02 PM