Dynamic column value

[HansBaeten]

Hi,

I want to create a dynamic segment value based on a what if parameter (or another variable)

Let's take following scenario:

I have 2 source systems (A and B) with sales data per company and market sales data

- The global average value of system A = 100
- The global average value of system B = 200

I have created a what if parameter in order to be able to "play" with those average values.

If the what if parameter = 10%, the global average value of system A = 110 and system B = 220.

So far so good. There isn't any issue with the averages or the parameter.

But based on this "new" average value (which is based on the original average value * value in the what if parameter), I want to create a segment per company.

For example: what if parameter = 10%

Sales value of a company in system A = 120 and in system B = 150.

- 120 is greater then 110 (= sales value of the company versus the global average value, adjusted with the what if parameter)
- 150 is below 220

For this company I want to create a segment value = "Potential"

Suppose what if parameter = 30%
- 120 is below 130
- 150 is below 260

In this case, the segment for this company should be value = "Abandon"

Is it possible to create such a scenario in Power BI? A calculated column doesn't take into account the what if parameter. A measure gives me the correct results in a table view. But I can't use this measure as a legend in a bar chart or a map for example.

Who can help me? 🙂

Regards,
Hans

[sturlaws]

Hi @HansBaeten,

a visual mockup of you desired outcome would be helpfull. A sample dataset would also be of great help.

One way of doing this, that will work in some scenarios, is to add a stand-alone table with your segments as rows.

Then for each segment create a measure like this:

tmpPotential =
CALCULATE (
    DISTINCTCOUNT ( Companies[Company] );
    FILTER (
        Companies;
        Companies[Sales value system A] > [ManipulatedGlobalAverageSystemA]
            && Companies[sales value system B] < [ManipulatedGlobalAverageSystemB]
    )
)

where

ManipulatedGlobalAverageSystemA =
CALCULATE (
    SUM ( 'Global averages'[Global average] ) * ( 1 + [Parameter Value] );
    FILTER ( 'Global averages'; 'Global averages'[System] = "SystemA" )
)

Then create this measure

Companies pr segment =
SWITCH (
    TRUE ();
    SELECTEDVALUE ( Segments[Segment] ) = "Potential"; [tmpPotential];
    SELECTEDVALUE ( Segments[Segment] ) = "Abandon"; [tmpAbandon];
    0
)

Now you can create a bar chart with Segments[Segment] as legend, and [Companies pr segment] as value. This will respond to changes in the parameter. But it will not work as a legend on a map, because there is no relationship between the Segments-table and the companies-table

[HansBaeten]

Hi @sturlaws,

thanks for your reply!

See screen below. My measures Segm_CRM_AvgValueAdj and Segm_RedData_AvgValueAdj are based on the slicer value and the global averages Segm_CRM_Netvalue and Segm_RedData_Netvalue. The last column in the table, CalculatedSegment, is what I want to achieve.

I'm not able to upload a mockp pbix file. Maybe I can sent it to you?

[sturlaws]

unfortunately the forum can't handle uploads directly, upload to dropbox, google drive or similar, and share the link

[HansBaeten]

This is the link to the pbix file with some sample data:

https://drive.google.com/drive/folders/1nLeyjZW9ISmH6eU_QGZZS3x0HksjYKV4?usp=sharing

[sturlaws · Accepted solution]

In this file: .pbix

I have added a stand-alone table with the segments. A stand-alone table has no relationship with any other table.

I have reused the code you made for the calculated column [CalculatedSegment], and created a measure called [Segment as legend]. Segments[Segment] and [Segment as legend] is used in the bar chart I have added.

I then took you calculated column [CalculatedSegment] and turned it into a measure, [CalculatedSegmentMeasure]

In order to make it possible to crossfilter from the bar chart to the table, I created a flag-measure which responds to changes in the Whatif-parameter, [SegmentFilterFlag]. I then added this flag to the visual filters of the table and set it to [SegmentFilterFlag]>=1. So if you click on the 'abandoned '-bar in the bar chart, only the entities which satisfy the criterias for abandon will show up in the table.

[HansBaeten]

Hi @sturlaws , great. This works.

One more question: is it also possible to use the legend on a map? Currently there is no relationship between my FactSegmentation and the segment stand alone table ((which is logic).

My goal is to have the segments as a legend on the map below (with the different colors per segment as in the bar chart):

[sturlaws]

The map-visual will only accept a Column as input in the Legend, and it is not possible have the calculated column change value based on parameter or slicer.

Two workarounds:

Create the map as a R- or Python-visual. I am no R-expert, nor Python, so this you would have to figure out for your self. I occasionally make use of R-visuals, and it works fine. But usually these visuals don't quite have the same look and feel as the regular visuals, so in a report they sometimes look a bit odd.

Make use of bookmarks:

Instead of a slicer create buttons with the desired percentages. For each button, create a corresponding segment column. Create one map for each button/column, with the corresponding column as legend. Use the buttons to toggle visibility of the corresponding map.