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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ChrisFromOhio
Helper I
Helper I

Calculate Sum of different item in every line

Hello - I have a data table that is similar to the following:

 

TypeMeasureValue
ContractualSales A5
TacticalSales A4
ContractualSales B6
TacticalSales B1
ContractualSales C8
TacticalSales C5
ContractualSales D2
TacticalSales D4
CostCost20

 

And what I am trying to do is create a calculated measure that will provide the Cost in each row (plan is to do % of cost, but I can do that final step). The result would look like the following:

 

 Contractual Tactical Cost
MeasureValueCostValueCostValue
Cost    20
Sales A520420 
Sales B620120 
Sales C820520 
Sales D220420 

 

How would I go about writing the calculated measure for this?

1 ACCEPTED SOLUTION
v-kaiyue-msft
Community Support
Community Support

Hi @ChrisFromOhio ,

 

Please follow these steps:

1.Create a metric and get the corresponding value.

_Value = SUM('Table'[Value])

2.Create another metric and get the corresponding cost.

_Cost =

IF(MAX('Table'[Type]) = "Cost",

BLANK(),

SUMX(FILTER(ALL('Table'),'Table'[Type]="Cost" && 'Table'[Measure] = "Cost"),'Table'[Value]))

3.Create a matrix with the corresponding rows, columns, and values as shown below.

vkaiyuemsft_0-1705978149372.png

4.The result obtained is shown below.

vkaiyuemsft_1-1705978149374.png

As far as I know, power bi is designed in such a way that if you want to hide _Cost under Cost, you need to manually drag its column width (place the cursor near the column, and drag it when the cursor turns into the double-arrow style as shown in the picture below) until it is hidden.

vkaiyuemsft_2-1705978315413.png

 

the result is shown in the following figure.

vkaiyuemsft_3-1705978340141.png

You can also submit an idea for it at https://statics.teams.cdn.office.net/evergreen-assets/safelinks/1/atp-safelinks.html and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-kaiyue-msft
Community Support
Community Support

Hi @ChrisFromOhio ,

 

Please follow these steps:

1.Create a metric and get the corresponding value.

_Value = SUM('Table'[Value])

2.Create another metric and get the corresponding cost.

_Cost =

IF(MAX('Table'[Type]) = "Cost",

BLANK(),

SUMX(FILTER(ALL('Table'),'Table'[Type]="Cost" && 'Table'[Measure] = "Cost"),'Table'[Value]))

3.Create a matrix with the corresponding rows, columns, and values as shown below.

vkaiyuemsft_0-1705978149372.png

4.The result obtained is shown below.

vkaiyuemsft_1-1705978149374.png

As far as I know, power bi is designed in such a way that if you want to hide _Cost under Cost, you need to manually drag its column width (place the cursor near the column, and drag it when the cursor turns into the double-arrow style as shown in the picture below) until it is hidden.

vkaiyuemsft_2-1705978315413.png

 

the result is shown in the following figure.

vkaiyuemsft_3-1705978340141.png

You can also submit an idea for it at https://statics.teams.cdn.office.net/evergreen-assets/safelinks/1/atp-safelinks.html and wait for users with the same needs as you to vote for you to help make it happen as soon as possible.

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@ChrisFromOhio , Try a measure like

 

calculate(Sum(Table[Cost]), Table[Type]= "Cost")

 

or

 

calculate(Sum(Table[Cost]), filter(all(Table),  Table[Type]= "Cost") )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!