The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
Solved! Go to 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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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, @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.
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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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, @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?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
Good point, using the existing data where possible would be better, thanks!
Best Regards,
Link Chen
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
@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.
@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
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
99 | |
81 | |
62 | |
54 |
User | Count |
---|---|
249 | |
119 | |
115 | |
94 | |
70 |