Totals in Matrix visual should have avg and summation

[Jessica_17]

Hello All,

I have a matrix visual which contains dollars and percentage values. 

I would like the total value for Dollar part show summation and % part should avg of how much rows are being present.

I am not able to perfrom this, can anyone please help?

[Ritaf1983 · Accepted solution]

Hi @Jessica_17 
Try this 2 measures :

Dollars_ = sum('Table'[Dollars])

%plan_ = sum('Table'[%Plan])/DISTINCTCOUNT('Table'[Quarters])

Pbix is attached.

If it still doesn't work 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ritaf1983 · Accepted solution]

Hi @Jessica_17 
Try this 2 measures :

Dollars_ = sum('Table'[Dollars])

%plan_ = sum('Table'[%Plan])/DISTINCTCOUNT('Table'[Quarters])

Pbix is attached.

If it still doesn't work 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Jessica_17]

HI @Ritaf1983 
Thanks for the solution, for percetage part it is working successfully, but for dollars part , I have if condition and which I am calling various measures to get sum, in that situation I cannot apply sum(), can you please provide another solution for this part

[Anonymous · Accepted solution]

Hi @Jessica_17 ,

I think you can try sumx() function to create a new [Dollars] measure based on old one.

NewDollars =
SUMX ( VALUES ( 'TableName'[Quarter] ), [Dollars] )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @Jessica_17 
For a more detailed solution, i need data to work with ...
so 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Jessica_17]

Hello @Ritaf1983 

Quarter	BP	TY	BP - TY
Q1	45	43	2
Q2	43	67	-24
Total	88	110	-22

I have BP and TY measure as sum of particular columns
BP -> sum(BP)
TY -> sum(TY)

BP- TY -> if(isblank(TY),0,BP - TY)
but total value are showing different regardless of what quarter is selected, and currently I have 4 quarters

Total

100

127

-27

It is ignoring any filters applied, and showing total of whole data in matrix visualization.

[Ritaf1983]

Hi @Jessica_17 

This is the table of the final results of the calculations, not data that can be worked with. Please share the table of the raw data. If some of the columns are sensitive, please keep only what is relevant to the question.

[Jessica_17]

Hello @Ritaf1983 

This is the raw table A

code	wm_year	wm_month	wm_quarter	Amt	Dept
6.52E+08	2022	6	Q2	1000	D46
5.65E+08	2022	6	Q2	78789	D46
5.64E+08	2022	6	Q2	34534	D46
5.72E+08	2022	6	Q2	4657	D46
5.81E+08	2022	6	Q2	34231	D64
5.56E+08	2022	7	Q2	45645	D64
5.67E+08	2022	7	Q2	6768	D64
5.74E+08	2022	7	Q2	5454	D64
288873	2022	7	Q2	3453	D64
5.86E+08	2022	7	Q2	3453	D64

This is raw table B

code	omni_dept_nbr	plan
########	46	0
4677560	46	0
4696115	46	0
4630555	46	0
258399	46	0
########	64	0
4664251	64	0
########	64	0
########	64	0
4073687	64	0

 both are connected via column code-> B to A -> one to many
BP = sum(B[plan]) 
TY = sum(A[Amt])
BP - TY = IF(ISBLANK([TY]),0,([BP] - [TY]))