Forum Discussion
Calculate Difference between Rows for a given Measure
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.
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% |
Hi kulpushu ,
The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a columnColumn = 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.
4 Replies
Hi kulpushu ,
The Table data is shown below:
Please follow these steps:
1. Use the following DAX expression to create a columnColumn = 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.Thanks a lot Anonymous -- 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
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?
