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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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