Compare treatments exclusively within the same fields to evaluate yield differences

[pmunoz]

Creating Comparative Graphs for Treatment Efficacy.

Good morning,

I hope this message finds you well. This is my first time reaching out here, so please forgive any missteps on my part.

I've been tasked with creating a comparative graph for a client specializing in trials. They've applied various treatments across different fields and have asked for a graph comparing the efficacy of these treatments. Specifically, they want to discern which treatments performed better or worse and how they fared within each field.

The client's request is to compare treatments only within the same field or fields.

For instance, if Treatments X and Y (there are many) were applied across Fields 1, 2, 3, 4, and 5, they're interested in a graph that exclusively displays the performance of these treatments within the same set of fields.

Essentially, they want a graph that represents the shared fields where two or more treatments have been applied.

Could someone kindly guide me through this process? I'm finding it quite challenging to fulfill this request, and any assistance would be greatly appreciated.

It's important to note that the client is primarily interested in comparing the fields based on the "TREATMENT CODE," which combines the field name and the treatment itself (e.g., "E12E27").

Key specifications:

• Treatments: Multiple (2+).
• X-axis (Fields): Limited to mutual instances. Although when I select two "Treatments," it currently includes fields with only one treatment, the client specifically desires information regarding fields highlighted in orange where both treatments have been applied—those shared fields with various treatments.

 

Thank you in advance for any help you can offer.

Your expertise in navigating this matter would be immensely valuable : )

[Martin_D · Accepted solution]

Hi @pmunoz ,

The reason why you are seeing this result is because neither the measure nor the sumarizing visual provides the trial context, whereas in the other chart the visual provides the trial context on the x-axis. In this case, the the summarzing visual includes per treatment all yield values per treatment if there is at least any yield value in any treatment included in your filters. This includes also yield values from trials that have a yield value in only one of the treatment - simply because the visual doesn't know which yield value comes from which trial.
There are two ways to solve this:

1.  Add the trial context to the visual: Very easy, use stacked column chart, use treatment as the x-axis and trial as the legend, and turn on total labels as described earlier.
2. Add the trial context to the measure.

There is no right or wrong approach, it depends on your requirements. Use the visual approach if you want to keep the measure universal and potentially use it in other visuals where you want to see the split by something else than trials. Use the measure approch if you want to make sure that the evaluation "yield value for every treatment" shall always be done by trial, even if there is an aggregtion by another category in a visual.
For the measure solution, this is the code:

Total Yield, Combined Treatments Only, per Trial = 

VAR _CalculationPerTrial =
    ADDCOLUMNS (
        SUMMARIZE ( 
            'data',
            'data'[TRIAL]
        ),
        "@Yield",

        // get list of all selected treatments
        VAR _SelectedTreatments = ALLSELECTED ( 'Treatment'[TREATMENT] )

        // check for each selected treatment whether there is data in the current context, e.g., a trial
        VAR _TreatmentsAndValues = 
            CALCULATETABLE (
                ADDCOLUMNS (
                    _SelectedTreatments,
                    "@Value",
                    CALCULATE (
                        [Total Yield]
                    )
                ),
                ALLSELECTED ( 'Treatment'[TREATMENT] )
            )

        // count number of treatments w/ value
        VAR _NumberOfAppliedTreatments = COUNTX ( _TreatmentsAndValues, [@Value] )

        // count number of selected treatments, with or without value
        VAR _NumberOfSelectedTreatments = COUNTROWS ( _SelectedTreatments )

        RETURN

        IF (
            _NumberOfAppliedTreatments = _NumberOfSelectedTreatments, // only if there are values for all selected treatments in the current context, e.g., a trial,
            [Total Yield]                                             // then show the value.
        )                                                             // otherwise not.
    )

RETURN

SUMX ( _CalculationPerTrial, [@Yield] )

Kind regards,
Martin

[pmunoz]

Hi @Martin_D, 

Thank you very much for your continued support.

I've prepared this Excel file based on confirmed client information, ensuring sensitive details are removed or amended.

Should you need any more information, please let it me know.

BR,

Pablo

[Martin_D]

Hi @pmunoz ,

Please check the attached file for the solution.

The masure, as described earlier, is:

Total Yield, Combined Treatments Only = 

// get list of all selected treatments
VAR _SelectedTreatments = ALLSELECTED ( 'Treatment'[TREATMENT] )

// check for each selected treatment whether there is data in the current context, e.g., a trial
VAR _TreatmentsAndValues = 
    CALCULATETABLE (
        ADDCOLUMNS (
            _SelectedTreatments,
            "@Value",
            CALCULATE (
                [Total Yield]
            )
        ),
        ALLSELECTED ( 'Treatment'[TREATMENT] )
    )

// count number of treatments w/ value
VAR _NumberOfAppliedTreatments = COUNTX ( _TreatmentsAndValues, [@Value] )

// count number of selected treatments, with or without value
VAR _NumberOfSelectedTreatments = COUNTROWS ( _SelectedTreatments )

RETURN

IF (
    _NumberOfAppliedTreatments = _NumberOfSelectedTreatments, // only if there are values for all selected treatments in the current context, e.g., a trial,
    [Total Yield]                                             // then show the value.
)                                                             // otherwise not.

BR,

Martin

[pmunoz]

Hi @Martin_D,

Great news—it's working now! Thanks a lot for your help.

I have a quick question, please. I successfully changed the current X variable to TRIAL in the graph, and it works perfectly. However, when I attempt to switch the current X variable (TRIAL) to the desired variable by the client, TREATMENT CODE (which is a hidden column in the same "data" table), I'm not getting any results. It is blank.

Is there a solution to address this issue?

Thanks once again,

Pablo

[pmunoz]

Hi @Martin_D,

I just realized another aspect.

On the left side, there's an additional graph I created depicting:

• X = TREATMENT.

• Y = Your measure "Total Yield, Combined Treatments Only."

I'm interested in obtaining the totals from the right graph, in the left graph. Example:

• If the right graph shows Treatment A with values of 1, 2, 3 in different fields, I'd like to see the sum (6) reflected on the left graph.

• Similarly, if the right graph displays Treatment B with values of 1, 1, 1 in different fields, I aim to observe the total (3) on the left graph.

In essence, I want to understand the overall yield per treatment in these fields.

How can I achieve this? The current numbers don't match.

Thanks once again,

Pablo

[Martin_D]

You can choose the stacked bar chart as the second graph and turn on total labels to get the total:

[Martin_D]

Hi @pmunoz ,

The pattern in the measure is always the same. It's specific to what you want to show on your x-axis as the legend, or more general, the mearue defines all of what (in my measure: TREATMENT, in my visual: the legend) must have values to show values. If you change the what on your x-axis resp. in the legend you need to change the what in the masure as well.
Kind regards,
Martin

[pmunoz]

Hi dear @Martin_D,

Thank you once again for your valuable feedback.

I've carefully reviewed your insights, and while I believe I grasp the concept, I'm encountering some difficulty aligning the numbers on both graphs.

To provide you with a clearer picture, I've attached screenshots with annotations within the same Excel file (tab 20240306).

I hope this visual representation better conveys my current situation.

Appreciate your continued assistance.

Kind regards,

Pablo

[Martin_D · Accepted solution]

Hi @pmunoz ,

The reason why you are seeing this result is because neither the measure nor the sumarizing visual provides the trial context, whereas in the other chart the visual provides the trial context on the x-axis. In this case, the the summarzing visual includes per treatment all yield values per treatment if there is at least any yield value in any treatment included in your filters. This includes also yield values from trials that have a yield value in only one of the treatment - simply because the visual doesn't know which yield value comes from which trial.
There are two ways to solve this:

1.  Add the trial context to the visual: Very easy, use stacked column chart, use treatment as the x-axis and trial as the legend, and turn on total labels as described earlier.
2. Add the trial context to the measure.

There is no right or wrong approach, it depends on your requirements. Use the visual approach if you want to keep the measure universal and potentially use it in other visuals where you want to see the split by something else than trials. Use the measure approch if you want to make sure that the evaluation "yield value for every treatment" shall always be done by trial, even if there is an aggregtion by another category in a visual.
For the measure solution, this is the code:

Total Yield, Combined Treatments Only, per Trial = 

VAR _CalculationPerTrial =
    ADDCOLUMNS (
        SUMMARIZE ( 
            'data',
            'data'[TRIAL]
        ),
        "@Yield",

        // get list of all selected treatments
        VAR _SelectedTreatments = ALLSELECTED ( 'Treatment'[TREATMENT] )

        // check for each selected treatment whether there is data in the current context, e.g., a trial
        VAR _TreatmentsAndValues = 
            CALCULATETABLE (
                ADDCOLUMNS (
                    _SelectedTreatments,
                    "@Value",
                    CALCULATE (
                        [Total Yield]
                    )
                ),
                ALLSELECTED ( 'Treatment'[TREATMENT] )
            )

        // count number of treatments w/ value
        VAR _NumberOfAppliedTreatments = COUNTX ( _TreatmentsAndValues, [@Value] )

        // count number of selected treatments, with or without value
        VAR _NumberOfSelectedTreatments = COUNTROWS ( _SelectedTreatments )

        RETURN

        IF (
            _NumberOfAppliedTreatments = _NumberOfSelectedTreatments, // only if there are values for all selected treatments in the current context, e.g., a trial,
            [Total Yield]                                             // then show the value.
        )                                                             // otherwise not.
    )

RETURN

SUMX ( _CalculationPerTrial, [@Yield] )

Kind regards,
Martin

[pmunoz · Accepted solution]

Million thanks, @Martin_D! It worked perfectly!

[Martin_D]

Hi @pmunoz ,

Happy to hear! Would you mind marking the solution as accepted solution if it works?

Thank you!
Kind regards,

Martin

[pmunoz]

Sure, done! Thank you!

[Martin_D]

Hi @pmunoz ,

One option to solve this would be to write a measure that checks whether there are treatment values for each selected field in the context (i.e., in your chart: treatment code) and, if there is at least one field with no value, then return no value for any field. This way you would only see those treatment codes for which there are values in all selected fields.
If you need further guidance, would there be a way to share this file or a file with sample/anonymized data to build upon?

BR

Martin