Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create a DAX table and measure

Goal is to have a measure calculating a percentage, but I first need help with setting up the data.   I'm lost trying to create the DAX measure that calculates "the percentage of people who grew by...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous  - Try the following measure:

    % Improved = 
var pre1 = SELECTCOLUMNS(FILTER(Table1,Table1[PreOrPost]="Pre"),"LabelIDAndName",Table1[LabelID] & Table1[Name], "Pre", [Score])
var post2 = SELECTCOLUMNS(FILTER(Table1,Table1[PreOrPost]="Post"),"LabelIDAndName",Table1[LabelID] & Table1[Name], "Post", [Score])
var combined = NATURALINNERJOIN(pre1,post2)
var change = ADDCOLUMNS(combined,"Change",[Post]-[Pre])
return DIVIDE(
    COUNTROWS(FILTER(change,[Change]>0)),
    COUNTROWS(change)
)

    You could use the split into a Calculated Table and a Measure - the "Return" portion would be the measure.

     

    You could also Pivot the table in Power Query - Pivot on the PreOrPost column, with Score as the Pivoted column.

    Hope this helps,

    Nathan

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous  - Try the following measure:

% Improved = 
var pre1 = SELECTCOLUMNS(FILTER(Table1,Table1[PreOrPost]="Pre"),"LabelIDAndName",Table1[LabelID] & Table1[Name], "Pre", [Score])
var post2 = SELECTCOLUMNS(FILTER(Table1,Table1[PreOrPost]="Post"),"LabelIDAndName",Table1[LabelID] & Table1[Name], "Post", [Score])
var combined = NATURALINNERJOIN(pre1,post2)
var change = ADDCOLUMNS(combined,"Change",[Post]-[Pre])
return DIVIDE(
    COUNTROWS(FILTER(change,[Change]>0)),
    COUNTROWS(change)
)

You could use the split into a Calculated Table and a Measure - the "Return" portion would be the measure.

 

You could also Pivot the table in Power Query - Pivot on the PreOrPost column, with Score as the Pivoted column.

Hope this helps,

Nathan