Forum Discussion
Under Over representation
@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)
@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)
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 Count | Age - Category | District - Category |
| 2 | 23 | z |
| 6 | 23 | z |
| 5 | 23 | x |
| 4 | 29 | s |
| 1 | 29 | s |
| 2 | 29 | z |
| 9 | 36 | z |
| 10 | 36 | x |
| 24 | 36 | x |
| 9 | 36 | z |
| 27 | 67 | z |
| Raw Pivot | Row Labels | 23 | 29 | 36 | 67 | Grand Total | |
| s | 5 | 5 | |||||
| x | 5 | 34 | 39 | ||||
| z | 8 | 2 | 18 | 27 | 55 | ||
| Grand Total | 13 | 7 | 52 | 27 | 99 | ||
| 1st Calculation | Age Category | ||||||
| 23 | 29 | 36 | 67 | ||||
| District Category | s | 0% | 100% | 0% | 0% | 100% | |
| x | 13% | 0% | 87% | 0% | 100% | ||
| z | 15% | 4% | 33% | 49% | 100% | ||
| Grand Total | 13% | 7% | 53% | 27% | 100% | ||
| 2nd Calculation | Age Category | ||||||
| 23 | 29 | 36 | 67 | ||||
| District Category | s | 0.00 | 14.14 | 0.00 | 0.00 | 1.00 | |
| x | 0.98 | 0.00 | 1.66 | 0.00 | 1.00 | ||
| z | 1.11 | 0.51 | 0.62 | 1.80 | 1.00 | ||
| 1.00 | 1.00 | 1.00 | 1.00 | 1.00 |
Thanks a Million 🙂
Thanks a million @Greg_Deckler - This worked perfectly 🙂
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
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)Anonymous Hooray!! 🙂