Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BKBO04
Frequent Visitor

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

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

ScenarioKPIValue
ACRevenue50
ACCosts20
ACSales 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.

9 REPLIES 9
Dangar332
Super User
Super User

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

 

Dangar332_0-1699535645716.png

Dangar332_0-1699535756013.png

 

 

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

HI, @BKBO04 

download pbix file

click HERE 

hi @BKBO04 ,

 

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

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
Super User
Super User

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

some_bih_0-1699535567027.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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
Frequent Visitor

In a table visual.

KPIValue by amount
Revenue5
Cost2
FreemanZ
Super User
Super User

how do you plan to present your measure?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.