Forum Discussion

nmeliasp's avatar
nmeliasp
Regular Visitor
7 years ago

This is a great start thank you. Now my next task is being able to measure the differences between these columns. is there a way to have an unpivoted version and a pivoted column? I find measuring the difference between te different completion columns is easier when each completion type is a column unless i'm missing something

AlB's avatar
AlB
Community Champion
7 years ago

nmeliasp

Well, all the info for calculating the differences is in what we did before. I don't know how you want to show it.  

You could, if the number of types of completions is low, have hard-coded measures like:

 

[Compl_X]= CALCULATE(COUNT(Table[Type_of_Completion ]);Table[Type_of_Completion="X")   

[Compl_Y]=CALCULATE(COUNT(Table[Type_of_Completion ]);Table[Type_of_Completion="Y")

 

and then create other measures simply with the difference.

 

Another option, although probably overly complicated for what you need, would be to add an additional column to the table shown earlier that's just a copy of Type_of_Completion:

 

Date                 Type_of_Completion      Type_of_Completion_Filter

10/01/18                   X                                          X

11/01/18                   Y                                          Y

10/15/18                   Z                                          Z    

 

You can then have have Type_of_Completion in rows of the matrix, Type_of_Completion_Filter in a slicer and by selecting one of X, Y or Z on the slicer you choose which one you ant to subtract from those in the rows. This would require a measure like this

 

Measure_Diff =
CALCULATE (
    COUNT ( Table[Type_of_Completion] ),
    ALL ( Table[Type_of_Completion_Filter] )
)
    - CALCULATE (
        COUNT ( Table[Type_of_Completion_Filter] ),
        ALL ( Table[Type_of_Completion] )
    )