topic Re: Two Grand Totals in Matrix Visual based on some conditions in DAX Commands and Tips

Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Thu, 12 Jan 2023 06:30:03 GMT

Hi All,

I am trying two create a simple matrix visual where I would like to show two grand totals at the end of the matrix.

The first grand total (total) is the overall sum of all the rows and another would be the sum based on some conditions, In the below data first total is the overall total, and the second total is where the quantity is 100

FYI - This is just sample data I am looking to apply this logic in a big matrix where I have to calculate the total based on certain values (sum where value is 1 or 2).

I have tried several ways but unable to achieve it. Anyone, please help me with this.

Data and expected output:

Person	Salary
A	100
B	200
C	400
D	100
E	100
Total	900
Total(100)	200

Your quick support will be highly appreciated.

Regards

Uphar

Re: Two Grand Totals in Matrix Visual based on some conditions

FreemanZ — Thu, 12 Jan 2023 06:44:39 GMT

Hi UpharTandon

what columns and rows do you have in your matrix visual?

you can also try to create two card visuals with two measures like this:

AllTotal = CALCULATE(SUM(TableName[Salary]), ALL(TableName)) 100Total CALCULATE(SUM(TableName[Salary]), TableName[Salary]=100)

Re: Two Grand Totals in Matrix Visual based on some conditions

tamerj1 — Thu, 12 Jan 2023 06:49:38 GMT

Hi UpharTandon
Perhaps something like this? See attached sample file

Re: Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Thu, 12 Jan 2023 06:52:19 GMT

Hi, I don't want to use card visuals as business requirement is to show everything on the matrix. Apart from this I have approx 30 columns in matrix.

Re: Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Thu, 12 Jan 2023 06:55:51 GMT

Hi Temerj, I don't want to create any additional rows in the table (total(100)). Just wanted to show this at the last in the matrix the same way the total column is showing in the above snapshot you had given.

Re: Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Thu, 12 Jan 2023 07:05:09 GMT

Just to provide more context in the below data I am summing up all the rows where flag ='1'.

Person	Salary	Flag	last year salary
A	100	1	80
B	200	0	180
C	400	0	400
D	100	1	90
E	100	1	80
Total	900	0	830
Total(100)	300		250

Similarly, I have many column to I would like to sum up based on the flag condition.

Re: Two Grand Totals in Matrix Visual based on some conditions

tamerj1 — Thu, 12 Jan 2023 07:23:30 GMT

UpharTandon
This is just a workaround as there no option is PowerBi (up to my knowlage) to have more than one grand total in a table or matrix.
Another worrkaround using a matrix visual as follows

Re: Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Thu, 12 Jan 2023 08:03:29 GMT

Hi tamerj1 , Thank you for sharing this but the only issue with my dataset is I will not be able to create any supportive table in the BI model as there are multiple categories available and against them we have flag ='1' to a particular date.

Re: Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Thu, 12 Jan 2023 12:31:01 GMT

Hi tamerj1 , I tried your solution but it is not working when we have more than one filter context in the table, and the matrix shows repetitive values.

Below is the query I have written

Total Salary =

IF (

SELECTEDVALUE ('Table'[Book Month] ) = "Total(SAS)",

CALCULATE ( SUM(VW_U_RECAP[Total SQC Awarded excluding UMS]),

VW_U_RECAP[Saas new unit] = 1,

ALL ( VW_U_RECAP)

SUM(VW_U_RECAP[Total SQC Awarded excluding UMS])

)

Do you have any other idea?

Re: Two Grand Totals in Matrix Visual based on some conditions

tamerj1 — Thu, 12 Jan 2023 13:04:46 GMT

UpharTandon
Can you provide sample data or sample file to work with?

Re: Two Grand Totals in Matrix Visual based on some conditions

sebouier — Fri, 13 Jan 2023 13:44:52 GMT

Please try this trick:

First, add a blank left calculated column in matrix visual. You'll get one subtotal row and one total row.

Then, change your measure:

Measure with subtotal = IF(ISINSCOPE([Your blank calulated column], CALCULATE(SUM([Your measure]),[Quantity]=100), IF(HASONEVALUE([Person]), [Your measure], CALCULATE(SUM([Your measure])) ) )

You'll have to rename the (sub)total titles

Re: Two Grand Totals in Matrix Visual based on some conditions

UpharTandon — Fri, 13 Jan 2023 08:28:21 GMT

Hi sebouier , The above-given solution will work only on one column condition but I have more than one column in filter context. For example, see the below data where I have calculated the total as well as total(flag=1).

Month	Name	Age	Department	Salary	Bonus	overtime	Flag
March	ABC	12	HR	100	50	20	0
March	ABD	13	QA	200	60	30	1
March	DEF	14	HR	300	70	40	0
March	GHI	15	QA	400	80	50	1
March	LMN	16	HR	500	90	60	0
April	GHI	17	QA	600	100	70	1
April	HIJ	18	HR	700	110	80	0
April	KLM	19	QA	800	120	90	1
April	DEF	20	HR	900	130	100	1
			Total	4500	810	540
			Total(Flag=1)	2900	490	340

Re: Two Grand Totals in Matrix Visual based on some conditions

sebouier — Fri, 13 Jan 2023 13:53:57 GMT

Sorry, I don't know any solutions if you have multiple left columns.

I don't know if it's acceptable for your project but I'd probably use card visual to customize your total, to make it look like it belongs to the table visual.

I'd put it at the top of the table. Then you don't have to care about the size of your table.