Total value in Slicer

[prakash11440278]

Hi Team,

I want to add the Global value in existing slicer, if the user select Global filter, need to display sum of all the region data. I dont want to add the Global value in existing table in my source. Please help.

Actual Data:

Expected Data:

[AllisonKennedy · Accepted solution]

@prakash11440278 Another option is to create a new DimSlicer table: 

Region Name

North America

Latin America and Asia Pacific

Africa and Middle East

Global

put this in a slicer. 

Create a new measure: 

Global Measure = SWITCH(DimSlicer[Region]

, "Global", CALCULATE( [Measure], ALL())

, CALCULATE( [Measure], Table[rnr_name] = SELECTEDVALUE(DimSlicer[Region]))

)

and use that in your visuals. Make sure the DimSlicer table is not related to any table in your data model and this should work.

[AllisonKennedy]

@prakash11440278 

Happy New Year! 

We haven't heard from you in a while, please can you let us know if you have been able to try any of the solutions suggested? Let us know which ones worked, or if none please let us know why so we can help further.

[v-xulin-mstf · Accepted solution]

Hi, @prakash11440278 

Depend on your need, I suggest you to do as below processes:

1.According to your actual data, I create a table as below:

2.According to your expected data, I create a table as below:

3.Create two measures as:

_id =
SWITCH (
    SELECTEDVALUE ( 'slicer table'[rnr_name] ),
    "Global", CALCULATE ( MAX ( 'Table1'[rnr_id] ), ALL () ) + 1,
    CALCULATE (
        MAX ( 'Table1'[rnr_id] ),
        FILTER ( 'Table1', 'Table1'[rnr_name] IN FILTERS ( 'slicer table'[rnr_name] ) )
    )
)

sum_region data =
SWITCH (
    SELECTEDVALUE ( 'Slicer Table'[rnr_name] ),
    "Global", CALCULATE ( SUM ( Table1[region data] ), ALL ( Table1 ) ),
    CALCULATE (
        SUM ( Table1[region data] ),
        FILTER ( 'Table1', 'Table1'[rnr_name] IN FILTERS ( 'slicer table'[rnr_name] ) )
    )
)

4.Drag columns into visuals.

Here is the demo, please try it: Total value in Slicer.pbix

Best Regards,

Link Chen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@v-xulin-mstf  and @v-chuncz-msft 

Your result is what the original poster has requested, but can you please elaborate on how to get the table in your step 2? Assuming the user has provided sample data (and even if they haven't), using the 'Enter Data' option to hard code data into Power BI that comes from a different data source is not something I recommend doing. It would be better to use the existing data where possible, perhaps using UNION or append queries?

[v-xulin-mstf]

Hi,@AllisonKennedy 

Good point, using the existing data where possible would be better, thanks!

Best Regards,

Link Chen

[AllisonKennedy · Accepted solution]

@prakash11440278 Another option is to create a new DimSlicer table: 

Region Name

North America

Latin America and Asia Pacific

Africa and Middle East

Global

put this in a slicer. 

Create a new measure: 

Global Measure = SWITCH(DimSlicer[Region]

, "Global", CALCULATE( [Measure], ALL())

, CALCULATE( [Measure], Table[rnr_name] = SELECTEDVALUE(DimSlicer[Region]))

)

and use that in your visuals. Make sure the DimSlicer table is not related to any table in your data model and this should work.

[amitchandak]

@prakash11440278 , You need to have that value in the table, You can create a separate table with these values.

Or Creat one row using enter data and merge it with your table at the source

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[prakash11440278]

Hi @amitchandak ,

If we do merge operation It will add that global value in the filter but we dont have matching records in other tables in our model. If we select global value report show blank values. 

[AllisonKennedy]

@prakash11440278  The global value you are looking for is a total row. You are correct to not add this to your data source/data model table. This should be in the visualization only. It can be displayed always, it doesn't need to be turned on/off with a filter. If you really need to you could create a button and duplicate chart, but I would suggest just adding a total row to your table visualization. 

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-tables

Slicers/filters will never change data tables, only visualizations, due to order of operations: Power BI Order of Operations