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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LocationUnknown
Frequent Visitor

Excluding parts of legend

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:

LocationUnknown_1-1662629858582.png

 

This works fine is just the fee. However once I add the legend to it it goes haywire: 

LocationUnknown_2-1662629879786.png

 

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.

 

3 REPLIES 3
amitchandak
Super User
Super User

@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.

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

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; 

ProjectCost ElementValue

Project 1

Arch fee458
Project 1QS fee987
Project 1MEP fee6457
Project 1MP fee416
Project 1Other Professional fee4654
Project 1Non fee cost 131254
Project 1Non fee cost 24687
Project 1Non fee cost 334556
Project 1Not fee cost 441547
Project 2Arch fee315
Project 2QS fee345
Project 2MEP fee398
Project 2Other Professional fee756
Project 2Non fee cost 131258
Project 2Non fee cost 287654
Project 2Non fee cost 432174
Project 2Non fee cost 567656
Project 3Arch fee897
Project 3QS fee879
Project 3MEP fee631
Project 3MP fee543
Project 3Other Professional fee489
Project 3Non fee cost 165842
Project 3Non fee cost 228569
Project 3Non fee cost 315698
Project 3Non fee cost 4564161


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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