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
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") )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |