HOW-TO::VISUAL CALCULATION::PERCENTAGE DELTA ACROSS MULTIPLE COLUMNS

ISSUE:

First, the screenshots:

 

INCORRECT

 

CORRECT

 

The Display slicer allows the user to show both velocity and volume, or one or the other.  When both choices are selected, there are no issues. When one choice is selected, the issue arises.

 

The issue is that we want to compute the difference in % of the current value from its previous occurrence, except for the first occurrence of the value since there is no other value preceding it.  In other words, as per the table above, when the series starts with 2023/01, followed by 2023/02, we want to know how much has the value increased or decreased in 2023/02 compared to 2023/01, and so on for every month thereafter, the value of which always gets compared to its prior occurence.  

 

Therefore, the first % column should be empty, but the second one should contain a value.  We should not have the first and second columns empty since we can compare the values of the second month to the first one.

 

The '% diff' column is computed using a visual calculation.  A few important points will be mentioned below regarding what can and cannot be referenced from within a visual calculation.  Also, the 'Value' column is computed by calling 2 different measures depending on what choice is selected in the 'Display' slicer.  

 

SOLUTION:

I had a measure defined in a table handling the measure selection from the slicer.  This table has no relationships. 

 

But that doesn't work. PBI Desktop will generate an error if you try to reference such a measure from within the visual calculation.   The measure was:

MeasureName = DISTINCTCOUNT('FieldSelector'[MeasureName]) 

 

The only thing that works inside the visual calculation and returns the proper count of items selected in the slicer is:  

COUNTROWS(ALLSELECTED([MeasureName]))

with the table name removed or you will get the 'can't find table' error, and even though outside the visual calculation this formula works fine:

DISTINCTCOUNT([MeasureName])

, just not from a visual calculation.

 

Trying to use:

DISTINCTCOUNT([MeasureName])
or
COUNTROWS(DISTINCTCOUNT([MeasureName]))

in the visual calculation will fail. 

 

Also, trying to use:

COUNTROWS(ALLSELECTED('FieldSelector'[MeasureName]))

will fail. 

 

There can be no table reference.  Only the following will work:

 

% diff = 
    VAR __offset = COUNTROWS(ALLSELECTED([MeasureName])) 
    VAR __value = [Value]  // value compared to value in previous column according to the offset
    VAR __prev_value = PREVIOUS ( [Value], __offset, COLUMNS )
    VAR __result = DIVIDE ( __value - __prev_value, __prev_value )
    RETURN
        FORMAT ( __result, "0.00 %" )

 

Hope this helps the puzzled ones.