Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all,
I am trying to make a graph that shows the % of a total cost for some of the components. This is done in a bar graph looking like this:
This works fine is just the fee. However once I add the legend to it it goes haywire:
It is only supposed to be showing the 5 cost elements used in the measure shown bellow:
Benchmarked Fee as % of Total =
Var _Fee =
CALCULATE(
SUM('d_Costs Data'[Value]),
'd_Costs Data'[Cost Element] = "Arch fee" ||
'd_Costs Data'[Cost Element] = "QS fee" ||
'd_Costs Data'[Cost Element] = "MEP fee" ||
'd_Costs Data'[Cost Element] = "PM fee" ||
'd_Costs Data'[Cost Element] = "Other Professional fee"
)
Var _AdjustedFee =
CALCULATE(
_Fee * DIVIDE(
[Selected Date Factor] * [Selected Location Factor],
10000
)
)
Return
DIVIDE( _AdjustedFee, [Benchmarked Value])
Where Selected Data and Location are used to benchmark the value so a chosen point. The Benchmarked Value is the total cost of the project at the chosen point. Cost data is a thin table with 3 columns, one is the key, the second is [cost element] which is the reason of the cost, and the third column is the value of the cost.
Basically, I only want the elements in the cost elements used in the dax measure to be shown.
@LocationUnknown , Add filter for all these. Example
Calculate(DIVIDE( _AdjustedFee, [Benchmarked Value]), filter( 'd_Costs Data',
'd_Costs Data'[Cost Element] in{ "Arch fee" ,"QS fee", "MEP fee" , "PM fee" ,"Other Professional fee" }))
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @amitchandak Unfortunately that will not work as it will always equal 100% as you are filtering the denominator to be the same as the numberator.
Unfortunately I can not provide the data but this is what it lookes like;
Project | Cost Element | Value |
Project 1 | Arch fee | 458 |
Project 1 | QS fee | 987 |
Project 1 | MEP fee | 6457 |
Project 1 | MP fee | 416 |
Project 1 | Other Professional fee | 4654 |
Project 1 | Non fee cost 1 | 31254 |
Project 1 | Non fee cost 2 | 4687 |
Project 1 | Non fee cost 3 | 34556 |
Project 1 | Not fee cost 4 | 41547 |
Project 2 | Arch fee | 315 |
Project 2 | QS fee | 345 |
Project 2 | MEP fee | 398 |
Project 2 | Other Professional fee | 756 |
Project 2 | Non fee cost 1 | 31258 |
Project 2 | Non fee cost 2 | 87654 |
Project 2 | Non fee cost 4 | 32174 |
Project 2 | Non fee cost 5 | 67656 |
Project 3 | Arch fee | 897 |
Project 3 | QS fee | 879 |
Project 3 | MEP fee | 631 |
Project 3 | MP fee | 543 |
Project 3 | Other Professional fee | 489 |
Project 3 | Non fee cost 1 | 65842 |
Project 3 | Non fee cost 2 | 28569 |
Project 3 | Non fee cost 3 | 15698 |
Project 3 | Non fee cost 4 | 564161 |
The select date factor and select time factor are both arbitary integers. Obiously there are more records than in the table above.
Hi, @LocationUnknown
This is really weird, if you just add a field to legend, the range of X axis should not change.
Can you provide a simplified pbix file for further research?
Best Regards,
Community Support Team _ Eason
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
122 | |
109 | |
60 | |
55 |