Calculate Difference between Rows for a given Measure

[kulpushu]

Hi all,

I want to calculate the relative value of the ATB by PDP visits between the control (off) and the variance (on) as listed below. The experiments are selected by a drop down selection and unfortunately given the set up of the data, one has to select the experiments by typing “Test 1” in the filter window and then selecting “Test 1 Off” and “Test 1 On” from the list. The tests have different names and the user will have to type them out and select the corresponding “off” and “On” selection. I have built the following table using calculation groups in Tabular Editor 3 with "Visits", "ATB Visits" and "ATB by PDP Visits" all being calculation items; however I have a separate measure defined for each calculation item, and thus there is a corresponding measure for ATB by PDP visits which is defined as the percentage of the sum of the ATB visits divided by the sum of the PDP visits.

ATBbyPDPVisits = DIVIDE(sum(FactExp[visitsATB]),sum(FactExp[visitsPDP]),0)

My goal is to find the percentage change in the ATB by PDP visits for the “Off” and “On” settings? That is, this calculation will be ((14.37-14.5)/14.37*100 = -0.9%) to be displayed as a separate KPI card, which will change when the selection of the experiment changes. Accounting for this, is there a simple way to calculate the change in Power BI given the fact that it will likely be a division across the selected rows (albeit it will only be for the two selected rows)?  Thank you all in advance!

Experiment Name	Visits	ATB Visits	ATB by PDP Visits %
Test 1 Off	5560	1567	14.37%
Test 1 On	1124	7889	14.50%

Expected Result:

ATB by PDP Visits Change %

0.90%

@OwenAuger 

[v-zhouwen-msft · Accepted solution]

Hi @kulpushu ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column

Column = LEFT([Experiment Name],6)

2.Use the following DAX expression to create a measure

Off = 
VAR _a = SELECTEDVALUE('Table'[Column])
VAR _b = _a & " Off"

RETURN CALCULATE(DIVIDE(SUM('Table'[ATB Visits]),SUM('Table'[PDP vistis]),0),'Table'[Experiment Name] = _b)

3.Use the following DAX expression to create a measure

On = 
VAR _a = SELECTEDVALUE('Table'[Column])
VAR _b = _a & " On"

RETURN CALCULATE(DIVIDE(SUM('Table'[ATB Visits]),SUM('Table'[PDP vistis]),0),'Table'[Experiment Name] = _b)

 4.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kulpushu]

Thanks a lot @v-zhouwen-msft -- I think your procedure works fine when one has specified experiment names and thus can use the "Selected Value" command. Is there a way if the experiment names are a bit jumbled up? I'd written "Test 1 Off" and "Test 1 On" to make things easier for you and other users to see. In reality I have a lot more variations with non-trivial names. Thanks again for spending your time - I understand I should have been more specific, but let me know if you have any other ideas -- Pushkar

[v-zhouwen-msft]

Hi @kulpushu ,

Do your experiment names contain numbers to distinguish between different experiments, e.g. 'T1', 'Test2', '3t'? If yes, you can use the function 'CONTAINSSTRING' with Switch to create a calculated column.

If there are no logic to follow, can you give some examples?

[kulpushu]

Hi @v-zhouwen-msft thanks for your suggestion and my apologies for my late reply. It seems like I have gone back and had a look at my data. We have decided to narrow down the list of the strings contained in the "Experiment Name" field to those that have a consistent naming format, and thus I think your solution should indeed work. Thanks again for your support!