topic Re: Issue with Matrix Visualization in DAX Commands and Tips

Issue with Matrix Visualization

soji — Wed, 11 Nov 2020 09:20:58 GMT

I have created a Matrix View in Power BI table as i wanted dynamic output based on the weeks selection. Here everything is working as i wanted except the Total - i need the average instead of total as highlighted in yellow colour. Can you please help.

Thanks

Soji

Re: Issue with Matrix Visualization

amitchandak — Wed, 11 Nov 2020 09:37:55 GMT

soji , Total seem like average, what is the problem?

isfiltered can help you in switching the total?

refer: https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

Re: Issue with Matrix Visualization

soji — Wed, 11 Nov 2020 09:54:29 GMT

Hi Amit,

I have manually added the average in the sample data which i shared but in BI i am getting total instead of average.

Thanks

Re: Issue with Matrix Visualization

amitchandak — Wed, 11 Nov 2020 10:06:47 GMT

soji , Assume you have sum measure

the create measures

average measure 1= [sum measure]/distinctcount(Table[Week])
average measure 2= [sum measure]/distinctcount(Table[emirate])

and use below measure in matrix

Switch( True() ,
isfiltered(Table[Week]) && not( isfiltered(Table[Emirate])) ,[average measure 1],
not(isfiltered(Table[Week])) || isfiltered(Table[Emirate]) ,[average measure 2],
[sum measure]
)

Re: Issue with Matrix Visualization

soji — Sun, 15 Nov 2020 05:44:31 GMT

Thanks for all responses.

I have managed to find the solution by creating a quick measure for the average.

Re: Issue with Matrix Visualization

wdx223_Daniel — Mon, 16 Nov 2020 01:50:23 GMT

=averagex(summarize(Factable,table[Emirate],DateTable[Week]),[Amount])