Power BI export with total row at the end of columns

[SalamRaed]

Greetings Power community, to make my question simple I created a flow to extract data from table in Power BI to CSV and email it to the user that click on the automate button, my problem is that there is no total row at the end of columns when I create the CSV file, so can someone help me to figure this out?

Many Thanks,

[lbendlin]

Create a DAX query that includes the total row.

[lbendlin]

Create a DAX query that includes the total row.

[SalamRaed]

Thank you for your reply Ibendlin, I am using the query from performance analyzer which is generating from a list table that shows the totals, but when I use the query to export it's shows everything except the totals, is there an extra line I need to add?

I am good at creating DAX measures but not long DAX queries so that's why I am asking where should I add the total.  

[lbendlin]

Hard to tell without seeing your query.

Let's say you have this visual

That has the following DAX query

// DAX Query
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('Table'[Custom], "IsGrandTotalRowTotal"),
			"CountCustom", CALCULATE(COUNTA('Table'[Custom]))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table'[Custom], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC, 'Table'[Custom]

If you run this in DAX Studio you get

Change the query slightly

EVALUATE 
SELECTCOLUMNS(
SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('Table'[Custom], "IsGrandTotalRowTotal"),
			"CountCustom", CALCULATE(COUNTA('Table'[Custom]))
		),"Custom",COALESCE([Custom],"Total"),"Count",[CountCustom])

to get the desired result

[SalamRaed]

Thank you so much, I will try that and let you know if that solution works.

[SalamRaed]

lbendlin, I tried to update the query but it keep throwing error, I add the evaluate at the bottom or replacing it with __Ds0core but all came with flow failure, I placed the query that been generated by Performance analyzer: 

// DAX Query
DEFINE
	VAR __DS0FilterTable = 
		TREATAS({"2023"}, 'dimDate'[fiscalYear])

	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('dimLocation'[locationName], "IsGrandTotalRowTotal"),
			__DS0FilterTable,
			"New_Location_Rank_and_total_Measure", 'Management Openings'[New Location Rank and total Measure],
			"Bottles_Added", 'Measure Table'[Bottles Added],
			"Invite_Rate", 'Measure Table'[Invite Rate],
			"Upgrade_Rate", 'Measure Table'[Upgrade Rate]
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(
			502,
			__DS0Core,
			[IsGrandTotalRowTotal],
			0,
			[New_Location_Rank_and_total_Measure],
			1,
			'dimLocation'[locationName],
			1
		)

	VAR __DS0CoreNoInstanceFiltersNoTotals = 
		FILTER(KEEPFILTERS(__DS0Core), [IsGrandTotalRowTotal] = FALSE)

EVALUATE
	GROUPBY(
		__DS0CoreNoInstanceFiltersNoTotals,
		"MinInvite_Rate", MINX(CURRENTGROUP(), [Invite_Rate]),
		"MaxInvite_Rate", MAXX(CURRENTGROUP(), [Invite_Rate]),
		"MinNew_Location_Rank_and_total_Measure", MINX(CURRENTGROUP(), [New_Location_Rank_and_total_Measure]),
		"MaxNew_Location_Rank_and_total_Measure", MAXX(CURRENTGROUP(), [New_Location_Rank_and_total_Measure])
	)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[IsGrandTotalRowTotal] DESC,
	[New_Location_Rank_and_total_Measure],
	'dimLocation'[locationName]

 Please let me know if that helps.

Thank you,

[lbendlin]

That looks like a matrix visual query (multiple Evaluates), not like a table visual query.  A DAX query in Power Automate can only return one result set.

Use DAX Studio to modify the DAX so it only returns one table.

[SalamRaed]

It is a Table not matrix and what I created is an automate button that once the user triggers it will extract the table with whatever filters they selected and email it to the user as CSV file, it all works but the extracted table is without the row total at the end unlike when you use the (Export Data) from the Header icons.

[lbendlin]

Try this one

EVALUATE SUMMARIZECOLUMNS(
			ROLLUPADDISSUBTOTAL('dimLocation'[locationName], "IsGrandTotalRowTotal"),
			TREATAS({"2023"}, 'dimDate'[fiscalYear]),
			"New_Location_Rank_and_total_Measure", 'Management Openings'[New Location Rank and total Measure],
			"Bottles_Added", 'Measure Table'[Bottles Added],
			"Invite_Rate", 'Measure Table'[Invite Rate],
			"Upgrade_Rate", 'Measure Table'[Upgrade Rate]
		)

[SalamRaed]

I tried your query, I added it and it showed all columns but without total rows, I believe I need to add a summation or some total function inside the summarizecoumns, or insert rows with sum() after the EVALUATE query above that you posted, I must find an efficient solution because I will add this automate button in other reports and dashboards so I will have to fix this total issue there too.

[lbendlin]

Sure - use whatever DAX you want. You can use DAX Studio to model the right query.

[SalamRaed]

I wish I could use DAX studio, the problem is they made a mistake with my PPU license and they did not add Pro to it, and for DAX studio to work with Power BI I will need a pro license!! thank you for your time and for trying to help I really appreciate it.

[lbendlin]

That is slightly incorrect.  As long as you have Build access  (XMLA endpoint access) you don't need a Pro license.