Forum Discussion

sufiyanakhtar's avatar
sufiyanakhtar
Helper I
4 years ago
Solved

Static Data in chart

Hey Power users,

I am looking forward to achieving something in Power BI. I have a bar chart and a slicer. The slicer contains the names of manufacturers and the bar chart shows the top 10 items supplied by all manufacturers. Now, when i select the slicer, the chart changes and the filter context is changed to the selected manufacturer in the slicer. I dont want this behaviour. Instead what should happen on the chart is that the same part numbers (top 10 across all manufacturers) should always show and whichever part number isnt a part of the selected manufacturer, it should just show 0. Can anyone help me with this?

Thank you.

amitchandak looking forward to your help!

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, sufiyanakhtar 

    Please try follow setps:

    1. Add a new calculated table showing the top 10 items from all manufacturers and build a one-to-many relationship with the fact table.

    Calculated table:

    New Table =
CALCULATETABLE (
    DISTINCT ( 'Table'[Items] ),
    TOPN (
        10,
        SUMMARIZE ( 'Table', 'Table'[Items], "Total Value", SUM ( 'Table'[Value] ) ),
        [Total Value], DESC
    )
)

    2. Add a measure like below:

    Total Value = SUM('Table'[Value])+0

    3. Replace your original 'Axis' and 'Values' in bar chart
    4. Show items with no data and filter out all blank values in the filter pane

    Result：

    Please check my sample pbix for more details.

     

    Best Regards,
    Community Support Team _ Eason

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    sufiyanakhtar , Try something like

     

    TOP N all = 
var _1  = topn(10,ALL('Item'[item]),CALCULATE([net], REMOVEFILTERS(manufactures)),DESC) 
return 
CALCULATE([Net], _1,VALUES('Item'[item]))
      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi, sufiyanakhtar 

        Please try follow setps:

        1. Add a new calculated table showing the top 10 items from all manufacturers and build a one-to-many relationship with the fact table.

        Calculated table:

        New Table =
CALCULATETABLE (
    DISTINCT ( 'Table'[Items] ),
    TOPN (
        10,
        SUMMARIZE ( 'Table', 'Table'[Items], "Total Value", SUM ( 'Table'[Value] ) ),
        [Total Value], DESC
    )
)

        2. Add a measure like below:

        Total Value = SUM('Table'[Value])+0

        3. Replace your original 'Axis' and 'Values' in bar chart
        4. Show items with no data and filter out all blank values in the filter pane

        Result：

        Please check my sample pbix for more details.

         

        Best Regards,
        Community Support Team _ Eason