topic Re: Divide each value of a column by a value of the same column in DAX Commands and Tips

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

BKBO04 — Thu, 09 Nov 2023 12:51:22 GMT

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.

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

FreemanZ — Thu, 09 Nov 2023 13:09:45 GMT

how do you plan to present your measure?

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

BKBO04 — Thu, 09 Nov 2023 13:13:33 GMT

In a table visual.

KPI	Value by amount
Revenue	5
Cost	2

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

some_bih — Thu, 09 Nov 2023 13:13:35 GMT

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"

)

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

Dangar332 — Thu, 09 Nov 2023 13:16:03 GMT

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))

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

BKBO04 — Thu, 09 Nov 2023 13:16:36 GMT

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.

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

BKBO04 — Thu, 09 Nov 2023 14:02:43 GMT

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

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

FreemanZ — Thu, 09 Nov 2023 14:23:12 GMT

hi BKBO04 ,

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

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

BKBO04 — Thu, 09 Nov 2023 14:31:39 GMT

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.

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

Dangar332 — Thu, 09 Nov 2023 14:48:44 GMT

HI, BKBO04

download pbix file

click HERE