Calculated Table Dax Code using Rollup needs Subtotal labels and sorting

[AndreasHermle]

Dear Experts: 
I would like to create a calculated table with subtotals by country, as depicted in this graphic. 

That is ...
- the Country Subtotals should say 'Subtotal' in the 'Category'-Column and
- the Country Subtotals should appear at the bottom row for each country.

- The Overall Total Row should say 'Total' in the Category Column

Now the Subtotals per Country appear randomly somewhere within the Country related rows and they do not say 'Subtotal' as a Label.
Moreover the 'Total Sales' Column should be sorted descending within the respective Country rows. Currently there is no sorting at all for the Total Sales Values. 
Is this possible by tweaking the below code? That would be fantastic. 

Help is very much appreciated. Thank you very much in advance. 

Regards, Andreas

The Calculated Dax-Code is as follows so far: 

SalesSummary-simple =

SUMMARIZE(

    'DataSource',

    ROLLUP(DimCtry[Country],DimCat[Category]),

        "Total Sales", [Total Sales]

      )

[Learner27]

Hai 

Could you please send the pbix file with some sample data hiding more sensitive data

[AndreasHermle]

Hi Learner, 

here you go. Thank you very much in advance for your valuable help. As I said before, your calculated table code is called SalesSummary, the second last calculated table in the Data Pane. 

https://drive.google.com/file/d/1MZjESs6GEcGij_SvBYU6e42t1nGlqY8W/view?usp=sharing

[v-hjannapu · Accepted solution]

Hi @AndreasHermle,

Thank you for reaching out to the Microsoft fabric community forum.

This is a work around you can go with this:

You can use the GROUP BY feature in Power Query to calculate total sales for each Country and Category. Then, create subtotals by grouping just by Country and label these as "Subtotal." Finally, add a row for the overall total labeled "Total." Combine all these results into one table and sort it so the subtotals appear under each Country group for a clear summary.

You can find official documentation  and screenshot here for reference:

How to GROUP BY or summarize rows - Power Query | Microsoft Learn

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Harshitha.

Community Support Team.

[AndreasHermle]

Great hjannapu, this did the trick. Thank you very much for your valuable help. Regards, Andreas

[AndreasHermle]

Dear Har**bleep**ha,

thank you very much for your great help. I wilm give it a try, as soon as I get home frm my vacation. Thank you, best regards, Andreas

[v-hjannapu]

Hi @AndreasHermle ,
If you get a chance to review my response, could you please confirm if the solution worked for you?
If it did, kindly consider marking it as "Accept as Solution" so that others with similar questions can find it more easily.If not, feel free to share more details so I can assist you further.

Thank you.

Harshitha.

Community Support Team.

[AndreasHermle]

Hi Learner, 

now here comes the file. Your code is integrated in a calculated table, named 'SalesSummary' and is the  second last calculated table at the bottom of the Data Pane. These sales figures along with other elements are all ficticious, so no need to hide any sensitive data.
Thank you very much in advance.
Oh, I am just looking for an icon to press so that I can upload my file. I cannot find one. I need to find out ...
Best Regards, Andreas

[AndreasHermle]

HI Learner, thank you very much for your offer. Great I will do that. Thank you very much again.

[Learner27]

SalesSummary =
VAR BaseTable =
ADDCOLUMNS (
'DataSource',
"Country", RELATED ( DimCtry[Country] ),
"Category", RELATED ( DimCat[Category] ),
"Total Sales", [Total Sales]
)
VAR SummaryTable =
SUMMARIZE (
BaseTable,
[Country],
[Category],
"Total Sales", SUM ( [Total Sales] )
)
VAR Subtotals =
ADDCOLUMNS (
SUMMARIZE ( BaseTable, [Country] ),
"Category", "Subtotal",
"Total Sales", CALCULATE ( SUM ( [Total Sales] ) )
)
VAR GrandTotal =
DATATABLE (
"Country", STRING,
"Category", STRING,
"Total Sales", CURRENCY,
{
{ BLANK (), "Total", CALCULATE ( SUM ( BaseTable[Total Sales] ) ) }
}
)
RETURN
UNION (
SummaryTable,
Subtotals,
GrandTotal
)

Subtotals per country, labeled as "Subtotal" in the Category column.
The total for all countries, labeled as "Total" in the Category column.

For sorting Desc order 

SortOrder = 
RANKX(
FILTER('SalesSummary', 'SalesSummary'[Country] = EARLIER('SalesSummary'[Country]) && 'SalesSummary'[Category] <> "Subtotal"),
'SalesSummary'[Total Sales],
,
DESC,
Dense
)

[AndreasHermle]

Thank you very much learner for your swift and great help. I guess we are close to being perfect. 
I am afraid to tell you that the code throws a couple of errors, I was able to correct a couple of these myself, but the other ones, I just could not resolve.
Please find my Comments below. My Comments all start with three forward slashes followed by MY COMMENT (///MY COMMENT: ...)
Thank you very much in advance for your valuable help.

SalesSummary =

VAR BaseTable =

ADDCOLUMNS (

'DataSource',

"Country", RELATED ( DimCtry[Country] ),

"Category", RELATED ( DimCat[Category] ),

"Total Sales", [Total Sales]

)

VAR SummaryTable =

SUMMARIZE (

BaseTable,

[Country],

[Category],

//MY COMMENT - Coding by you: "Total Sales", SUM ( [Total Sales] ), subsequent line coding by me

"Total Sales", [Total Sales]

)

VAR Subtotals =

ADDCOLUMNS (

SUMMARIZE ( BaseTable, [Country] ),

"Category", "Subtotal",

///MY COMMENT - Coding by you: "Total Sales", CALCULATE ( SUM ( [Total Sales] ) ), subsequent line coding by me

"Total Sales", CALCULATE ( [Total Sales] )

)

VAR GrandTotal =

DATATABLE (

"Country", STRING,

"Category", STRING,

"Total Sales", CURRENCY,

{

///MY COMMENT: The subsequent line throwing an error on: SUM ( BaseTable[Total Sales] ) )

{ BLANK (), "Total", CALCULATE ( SUM ( BaseTable[Total Sales] ) ) }

}

)

RETURN

UNION (

SummaryTable,

Subtotals,

GrandTotal

)

//Subtotals per country, labeled as "Subtotal" in the Category column.

//The total for all countries, labeled as "Total" in the Category column.

//For sorting Desc order

///MY COMMENT: Here I am getting almost everything underlined with the Exception of RANKX, FILTER, <> "Subtotal", DESC and Dense

SortOrder =

RANKX(

FILTER('SalesSummary', 'SalesSummary'[Country] = EARLIER('SalesSummary'[Country]) && 'SalesSummary'[Category] <> "Subtotal"),

'SalesSummary'[Total Sales],

,

DESC,

Dense

)