topic Sum Total of Measure Column in DAX Commands and Tips

Sum Total of Measure Column

Anonymous — Sat, 16 May 2020 14:30:31 GMT

I have several measures performing calculations in a matrix, the second column here is a measure of measures,

The third Column is a measure of the measure of the form:

CALCULATE(COUNTROWS(FILTER(ALL(_Measures),_Measures[measurename] > 100))).

The end goal here being, I would like to get the % of rows in the table that are greater than 100.

Any help is appreciated.

Re: Sum Total of Measure Column

Greg_Deckler — Sat, 16 May 2020 14:49:01 GMT

Anonymous I'm not sure what the question is here but I have not had my first cup of coffee yet this morning. 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.

But, could be a measures aggregation issue. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

Maybe a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376. Also, this Quick Measure, Measure Totals, The Final Word should get you what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Re: Sum Total of Measure Column

Anonymous — Sat, 16 May 2020 17:55:59 GMT

Thansk for responding Greg. I'll see if I can expand.

In the third column of the table I have 1's registered if the second column is ?100 and )'s if not. This column is a result of the measure :

Measure Name = CALCULATE(COUNTROWS(FILTER(ALL(_Measures),_Measures[other measure] > 100)))

which is a measure of another measure

I would like to calculate the total number of 1's and divide by the total number of rows to get the percentage. I originally thought the COUNTROWS measure would get the total, but it registered just the 1's - every way I try to sum those I can't get it to work.
More precisely I'd like to see the percentage of all rows where that column is >100.

What I've come across in trying to select the measure 'other measure' in an aggregate of any type, it is not selectable - I suppose because it is a measure column and not a table column -

In this project, I have several measures that filter into other measures for me to get to this far, I can't help but think that is my problem, but I need to capture the snapshots at multiple steps to meet the customer's goal.

Re: Sum Total of Measure Column

mahoneypat — Sat, 16 May 2020 21:40:43 GMT

One way to do this might be the following approach. Make a measure that does the following:

1. make a variable with GENERATESERIES() that goes from 1 to the # of measures you have

2. separately make a SWITCH() measure that returns each # based on 1 to your total # of measures, returning each of those measures to use in step 3. Use the Value column as your expression in the switch.

3. In another variable, do ADDCOLUMNS(tableinvariable1, "Result", [switch measure])

4. return DIVIDE(Countrows(Filter(#3variabletable, [Result]>100)), countrows(#3variabletable))

Sorry I didn't have time to type it out better, but I hope that gets you there.

If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat