Calculate Sum of different item in every line

[ChrisFromOhio]

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

Type	Measure	Value
Contractual	Sales A	5
Tactical	Sales A	4
Contractual	Sales B	6
Tactical	Sales B	1
Contractual	Sales C	8
Tactical	Sales C	5
Contractual	Sales D	2
Tactical	Sales D	4
Cost	Cost	20

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
Measure	Value	Cost	Value	Cost	Value
Cost					20
Sales A	5	20	4	20
Sales B	6	20	1	20
Sales C	8	20	5	20
Sales D	2	20	4	20

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

[v-kaiyue-msft · Accepted solution]

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.

4.The result obtained is shown below.

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.

the result is shown in the following figure.

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]

@ChrisFromOhio , Try a measure like

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

or

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