cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prakash11440278
Post Partisan
Post Partisan

Total value in Slicer

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:

prakash11440278_0-1608533948713.png

Expected Data:

prakash11440278_1-1608533981179.png

 

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1608685716972.png

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

v-xulin-mstf_1-1608685716974.png

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.

v-xulin-mstf_2-1608685716979.png

 

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.

View solution in original post

8 REPLIES 8
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

v-xulin-mstf
Community Support
Community Support

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:

v-xulin-mstf_0-1608685716972.png

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

v-xulin-mstf_1-1608685716974.png

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.

v-xulin-mstf_2-1608685716979.png

 

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.

View solution in original post

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


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi,@AllisonKennedy 

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

Best Regards,

Link Chen

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

amitchandak
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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
Super User
Super User

@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 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.