Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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?
Solved! Go to 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.
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.
@ChrisFromOhio , Try a measure like
calculate(Sum(Table[Cost]), Table[Type]= "Cost")
or
calculate(Sum(Table[Cost]), filter(all(Table), Table[Type]= "Cost") )