Forum Discussion
Under Over representation
Hi, I am trying to calculate an index to show over or under representation from values relative to the total.
the rows are dimension. so what I want to do is firstly the row calculation as shown already and then dividing it by the coloum total for each cell. e.g. the first cell should calculate 0.16% divided by 0.22%. Please help
@vikash_1108 - Funny, I thought this is where this was going, so he actually cooked it that way last night before I went to bed. I didn't publish it because this version of the measure actually returns the exact same numbers as if you take the first measure, add it to the visual, and then choose Show values as . Percentage of the total column. I just wanted to do it to prove I could do it. But, here it is with updated PBIX attached, same page 9, Table 9. The old code is lines with // comments. Finally, remember me when you answer, I almost lost track of this thread!!
Measure 9 Column % of Row % = VAR __Age = MAX([Age - Category]) VAR __Table = ADDCOLUMNS(SUMMARIZE(FILTER(ALL('Table (9)'),[Age - Category]=__Age),[District - Category]),"__Measure",[Measure 9 Row %]) // VAR __All = SUMX(__Table,[__Measure]) VAR __All = IF(HASONEVALUE('Table (9)'[District - Category]),CALCULATE([Measure 9 Row %],ALLEXCEPT('Table (9)','Table (9)'[Age - Category])),[Measure 9 Row %]) VAR __Cell = [Measure 9 Row %] RETURN //IF(HASONEVALUE('Table (9)'[District - Category]),DIVIDE(__Cell,__All,0),[Measure 9 Row %]) DIVIDE(__Cell,__All,0)@vikash_1108 - Oh, yes, I got it, duh. Updated PBIX, Page 17, Table (17). The second measure is identical to the reference changes in the measure and table names.
Measure 17 Row % = VAR __Age = MAX([Age - Category]) VAR __District = MAX([District - Category]) VAR __Cell = SUM([Member Count]) VAR __All = IF(HASONEVALUE('Table (17)'[District - Category]),SUMX(FILTER(ALL('Table (17)'),[District - Category] = __District),[Member Count]),SUMX(ALL('Table (17)'),[Member Count])) RETURN DIVIDE(__Cell,__All,0) Measure 17 Column % of Row % = VAR __Age = MAX([Age - Category]) VAR __Table = ADDCOLUMNS(SUMMARIZE(FILTER(ALL('Table (17)'),[Age - Category]=__Age),[District - Category]),"__Measure",[Measure 17 Row %]) // VAR __All = SUMX(__Table,[__Measure]) VAR __All = IF(HASONEVALUE('Table (17)'[District - Category]),CALCULATE([Measure 17 Row %],ALLEXCEPT('Table (17)','Table (17)'[Age - Category])),[Measure 17 Row %]) VAR __Cell = [Measure 17 Row %] RETURN //IF(HASONEVALUE('Table (17)'[District - Category]),DIVIDE(__Cell,__All,0),[Measure 17 Row %]) DIVIDE(__Cell,__All,0)
14 Replies
Anonymous - In Values area of the Visualizations pane, click the drop down arrow for your measure/column and the see screen shot:
Anonymous Try:
Percent Column Total = DIVIDE( SUM('R07_Table'[Value], SUMX( CALCULATETABLE( 'R07_Table', ALL(R07_Table[Category1]) ), [Value] ) )That's the basic concept from Chapter 4, Recipe 7 of DAX Cookbook. https://github.com/gdeckler/DAXCookbook Now, that will probably not work for you because you have, in effect, a measure in your matrix so you need to calculate that for the row/column you are in and then also compute that for the "ALL" case. However, I can't get specific because you haven't provided sufficient information. For example, if I knew what you were using for rows in your matrix I could probably be specific but you specifically cut that out of your screen shot. Knowing the underlying data structure and sample data would help even more.
So, at the end of the day, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.