Measure using different data within one column

[Pammy2411]

Hi,

I currently have my data structured in the following manner:

I am having trouble calculating within one column. For example, in my column KPI i have:

Product 1 Revenue

Product 2 Revenue

I am trying to calculate the Revenue Mix: Product 1 Revenue/ (Product 1 Revenue+Product 2 Revenue). Is there any way to create a measure that calculates this?

I tried to use the If function for KPI, in order to filter out the value, however, since the same KPI appears more than once in the table it tells me that I need one unique value. However, there is no way i can Sum (or any calculation of the sort) a string.

Thank you,

Pamela

[PowerDAX · Accepted solution]

Hi Pamela - you have a couple of options here.

1. When importing the data to Power BI, you could 'pivot' the data to make each KPI a column.  This is not best practice if there are multiple KPI(s) to calculate.  This is due to an increase in the number of columns and an increase in cardinality in values - thus higher memory consumption and slower calculations.  If you didn't have very many KPI(s) and not a bunch of rows, you would pivot the data and sum the columns as your calculation - i.e. Revenue - Product 1 = SUM( [Product 1 Revenue]), Revenue - Product 2 = SUM( [Product 2 Revenue]) with and end result measure of Revenue Mix = DIVIDE( [Revenue - Product 1], [Revenue - Product 1] + [Revenue - Product 2], 0)

2. When your values are in rows vs columns, you have to use a CALCULATE function.  For this calculation in particular, you could

    a) create measures for each KPI - i.e. Revenue - Product 1 = CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue"), Revenue - Product 2 = CALCULATE( SUM( [Value]), [KPI] = "Product 2 Revenue") - if those are required to be viewed individually - and an end result measure - i.e. Revenue Mix = DIVIDE( [Revenue - Product 1], [Revenue - Product 1] + [Revenue - Product 2], 0)

    b) create your end result measure w/o intermediate measures - i.e. Revenue Mix = DIVIDE( CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue"), CALCULATE( SUM( [Value]), [KPI] = "Product 1 Revenue" || [KPI] = "Product 2 Revenue"), 0)