Forum Discussion
Cannot add Nation values and Total values
Hi,
Need help in creating one more row similar to Total - Nation. Where Nation values will be fixed and Total values will change based on filter.
Hi manasapola ,
Well there isnt a direct way to include Nation and its constant value under same table as Country and Product are seperate columns whereas Nation is built-in.
However theres a workaround on this-
1. Create a custom Table-
go to->Modelling->New table-CustomTable =DATATABLE("Country", STRING, {{"Nation"}})
2. Create a measure that will show you constant value for nation which wont be affected by Product or Country.
Nation Sales =CALCULATE(SUM('Table'[Total Sales]),REMOVEFILTERS('Table'[Country]),REMOVEFILTERS('Table'[Product]))
3. Create 2 tables-
Add Nation and Nation sales into one and in other add Product, Country and Sales.
4. Add necessary slicers and nation sales wont change.
Note- You can even add a card to show a constant (100) sales and name it as nation.
Attached pbix for reference.
If this is not what you have expected, please provide a sample data in the form of pbix to assist you better.
Hope this helps!
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank You
6 Replies
Hi manasapola ,
If possible, could you please provide more details about your data? (exclude sensitive data) and your expected result. It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Shruti
Hi Shruti,
Nation will be sum of total sales but the value should be at Nation level where filter Country and Product should not get effected
Total will be default total provided by Power BI, on which filters will get effected.
Thanks in advance.Country Product Total Sales Nation 100 Australia Product 1 10 Australia Product 2 20 Africa Product 1 30 America Product 2 40 Total 100
Hi manasapola ,
Well there isnt a direct way to include Nation and its constant value under same table as Country and Product are seperate columns whereas Nation is built-in.
However theres a workaround on this-
1. Create a custom Table-
go to->Modelling->New table-CustomTable =DATATABLE("Country", STRING, {{"Nation"}})
2. Create a measure that will show you constant value for nation which wont be affected by Product or Country.
Nation Sales =CALCULATE(SUM('Table'[Total Sales]),REMOVEFILTERS('Table'[Country]),REMOVEFILTERS('Table'[Product]))
3. Create 2 tables-
Add Nation and Nation sales into one and in other add Product, Country and Sales.
4. Add necessary slicers and nation sales wont change.
Note- You can even add a card to show a constant (100) sales and name it as nation.
Attached pbix for reference.
If this is not what you have expected, please provide a sample data in the form of pbix to assist you better.
Hope this helps!
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank YouHi manasapola ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank YouHi @manasapola ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank YouHi @manasapola ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank You
