Forum Discussion

kulpushu's avatar
kulpushu
Frequent Visitor
2 years ago
Solved

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 unf...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
kulpushu
Frequent Visitor
2 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    kulpushu
    Frequent Visitor
    2 years ago

    Hi Anonymous 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!