Forum Discussion

rgoo's avatar
rgoo
Frequent Visitor
2 years ago
Solved

modifying table total row value

Is there a way to modify the total to a string value such as "SEA"?
the country column consists of a different countries and i want to be able to show SEA for SEA countries, Europe for EU countries etc.

Much appreciation for the help!

  • Hi rgoo -  In Power BI does not directly allow you to changing the text in the total row independently of the detail rows. The measure logic should handle how totals are represented.

     

    Create a sepearate measure for SEA and Europe and then combine them both.

    TotalSEA =
    IF(
    HASONEVALUE('YourTableName'[country]) &&
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Thailand", "Vietnam", "Indonesia"})) > 0,
    "SEA",
    BLANK()
    )

    Measure for Europe

     

    TotalEurope =
    IF(
    HASONEVALUE('YourTableName'[country]) &&
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Germany", "France", "Spain"})) > 0,
    "Europe",
    BLANK()
    )

     

    Combine them both using below measure:

     

    TotalRegion =
    IF(
    ISFILTERED('YourTableName'[country]),
    SWITCH(
    TRUE(),
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Thailand", "Vietnam", "Indonesia"})) > 0, "SEA",
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Germany", "France", "Spain"})) > 0, "Europe",
    "Other"
    ),
    "Total Region" // This value will appear in the total row
    )

     

    if any further modifications on region, you have to adjust it in combined measure only.

    add country to the column and total region measure to your table.

    Replace YourTableName as per your table details and column information at your end.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

1 Reply

  • Hi rgoo -  In Power BI does not directly allow you to changing the text in the total row independently of the detail rows. The measure logic should handle how totals are represented.

     

    Create a sepearate measure for SEA and Europe and then combine them both.

    TotalSEA =
    IF(
    HASONEVALUE('YourTableName'[country]) &&
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Thailand", "Vietnam", "Indonesia"})) > 0,
    "SEA",
    BLANK()
    )

    Measure for Europe

     

    TotalEurope =
    IF(
    HASONEVALUE('YourTableName'[country]) &&
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Germany", "France", "Spain"})) > 0,
    "Europe",
    BLANK()
    )

     

    Combine them both using below measure:

     

    TotalRegion =
    IF(
    ISFILTERED('YourTableName'[country]),
    SWITCH(
    TRUE(),
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Thailand", "Vietnam", "Indonesia"})) > 0, "SEA",
    COUNTROWS(FILTER('YourTableName', 'YourTableName'[country] IN {"Germany", "France", "Spain"})) > 0, "Europe",
    "Other"
    ),
    "Total Region" // This value will appear in the total row
    )

     

    if any further modifications on region, you have to adjust it in combined measure only.

    add country to the column and total region measure to your table.

    Replace YourTableName as per your table details and column information at your end.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!