Divide each value of a column by a value of the same column

[BKBO04]

Hi, I'm failing to create a measure. My data table looks like this:

Scenario	KPI	Value
AC	Revenue	50
AC	Costs	20
AC	Sales amount	10

For Scenario = AC and for each KPI the measure should calculate the value divided by the value of the Sales amount. This would return 5 for Revenue and 2 for Costs.

[Dangar332]

Hi, @BKBO04 

try below measure

Measure 3 = 
var a = CALCULATE(SUM('Table (2)'[Value]), 'Table (2)'[KPI]="sales amount",'Table (2)'[Scenario]=MAX('Table (2)'[Scenario]))
return
IF(MAX('Table (2)'[KPI])="sales amount",MAX('Table (2)'[Value]),DIVIDE(MAX('Table (2)'[Value]),a))

[BKBO04]

Does not work for me. It just returns one value for total.

[Dangar332]

HI, @BKBO04 

download pbix file

click HERE 

[FreemanZ]

hi @BKBO04 ,

how is the Scenario column presented, with a slicer or?

[BKBO04]

I use the Zebra BI table visuals with seperate buckets where I would put the AC, PL and FC measure so that I get three columns for each scenario.

[some_bih]

Hi @BKBO04 one of possible solutions: create 2 separate measure as following and you should have output as below.

Adjust MyTable name to your table name.

Measure_Costs_AC =

DIVIDE(

    CALCULATE(

        sum(MyTable[Value]),

        MyTable[KPI]="Costs",

        MyTable[Scenario]="AC"

    ),

    CALCULATE(

        sum(MyTable[Value]),

        MyTable[KPI]="Sales amount",

        MyTable[Scenario]="AC"

    )

)

Measure_Revenue_AC =

DIVIDE(

    CALCULATE(

        sum(MyTable[Value]),

        MyTable[KPI]="Revenue",

        MyTable[Scenario]="AC"

    ),

    CALCULATE(

        sum(MyTable[Value]),

        MyTable[KPI]="Sales amount",

        MyTable[Scenario]="AC"

    )

)

[BKBO04]

Thanks for your suggestion, but this was a simplified example. In reality I have more KPI than just Revenue an Costs. So the measure should work for each KPI.

[BKBO04]

In a table visual.

KPI	Value by amount
Revenue	5
Cost	2

[FreemanZ]

how do you plan to present your measure?