Cannot add Nation values and Total values

[manasapola]

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.

[v-sdhruv]

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

[v-sdhruv]

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

[v-sdhruv]

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

[v-sdhruv · Accepted solution]

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

[v-sdhruv]

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

[manasapola]

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