Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Under Over representation

Hi, I am trying to calculate an index to show over or under representation from values relative to the total.  

 

vikash_1108_0-1599251981121.png

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

2 ACCEPTED SOLUTIONS

@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)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@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)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

14 REPLIES 14
Greg_Deckler
Super User
Super User

@Anonymous - In Values area of the Visualizations pane, click the drop down arrow for your measure/column and the see screen shot:

Greg_Deckler_0-1599255078258.png

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Greg thanks for your suggestion - What you have given as solution is already done to get the initial table.  After getting the initial table I want the values in each cell to be divided by its relative column total to give over or under representation. 

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Member ID

Age - CategoryDistrict - Category
A23z
b23z
c23x
d29s
e29s
f29z
g36z
h36x
i36x
j36z
k67z

 

Hi greg

 

I have included sample data.

1. So in first instance you will have pivot of District (row) over Age (coloum), counting members.

2. Then at each district level you will find proportion of member count

3. then as final calc you will get the proportion of member count in (2) divided by total proprtion of member count for that column (i.e. Age)

 

Hope this will help you derive the formular

 

Thanks Vikash

@Anonymous - OK, I did it like this, not sure if it is correct or what you are looking for. First, I created a measure to calculate the % Total of Row since that is what your original image showed (hence why you have 100% in the row totals).

 

Measure 9 Row % = 
    VAR __Age = MAX([Age - Category])
    VAR __District = MAX([District - Category])
    VAR __Cell = COUNT([Member ID])
    VAR __All = IF(HASONEVALUE('Table (9)'[District - Category]),COUNTROWS(FILTER(ALL('Table (9)'),[District - Category] = __District)),COUNTROWS(ALL('Table (9)')))
RETURN
    DIVIDE(__Cell,__All,0)

 

Then I calculated the % column total of that like this:

 

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 __Cell = [Measure 9 Row %]
RETURN
    IF(HASONEVALUE('Table (9)'[District - Category]),DIVIDE(__Cell,__All,0),[Measure 9 Row %])

 

However, note that if you put the first measure in the matrix and then do the thing I mentioned originally with the Show value as | Percent of Column total, you get a different result so not sure which you want.

 

Anyway, PBIX file is attached below sig. Page 9, Table (9).


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg,

Thanks for your endurance in doing this. The first calculation you have done is correct. However, the second calculation is not giving the desired outcome. Following is what the 1st and then the 2nd calculation should give as outcome when we pivot it.

1st Calculation Age Category
23293667
District Categorys0%100%0%0%100%
x33%0%67%0%100%
z33%17%33%17%100%
Grand Total27%27%36%9%100%
2nd Calculation Age Category
23293667
District Categorys0.003.670.000.001.00
x1.220.001.830.001.00
z1.220.610.921.831.00
1.001.001.001.001.00

Thanks in advance. I think we are almost there.

Regards

Vikash

@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)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler @Ashish_Mathur

Thank you so much. This is exacktly what I wanted.

I have one more questions though. What would be the code for the 2 calculations if the data is already summarised. Below are the details of the summarised Raw table and what final results would be expected ..

Member CountAge - CategoryDistrict - Category
223z
623z
523x
429s
129s
229z
936z
1036x
2436x
936z
2767z

Raw Pivot Row Labels23293667Grand Total
s 5 5
x5 34 39
z82182755
Grand Total137522799
1st Calculation Age Category
23293667
District Categorys0%100%0%0%100%
x13%0%87%0%100%
z15%4%33%49%100%
Grand Total13%7%53%27%100%
2nd Calculation Age Category
23293667
District Categorys0.0014.140.000.001.00
x0.980.001.660.001.00
z1.110.510.621.801.00
1.001.001.001.001.00

Thanks a Million 🙂

@Anonymous - If I understand correctly, the only thing that would change would be:

Measure 9 Row % = 
    VAR __Age = MAX([Age - Category])
    VAR __District = MAX([District - Category])
    VAR __Cell = SUM([Member ID])
    VAR __All = IF(HASONEVALUE('Table (9)'[District - Category]),COUNTROWS(FILTER(ALL('Table (9)'),[District - Category] = __District)),COUNTROWS(ALL('Table (9)')))
RETURN
    DIVIDE(__Cell,__All,0)

For __Cell, COUNT would change to SUM 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

HI Greg,

I Also thought that would work, but it did not.

Regards

Vikash

@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)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks a million @Greg_Deckler - This worked perfectly 🙂

@Anonymous Hooray!! 🙂


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi,

Please share the exact result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.