Forum Discussion
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:
Expected Data:
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.
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.
8 Replies
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.
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.
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?
Good point, using the existing data where possible would be better, thanks!
Best Regards,
Link Chen
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
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
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.
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.
