Help with Dax formula for Matrix

[rbartlejr]

I'm having an issue with a DAX formula. I've tried using the FILTER function but I just can't get it correct (I'm still very novice with DAX). I have a database in Acess that comes from an Excel report. It can be found here:

https://www.dropbox.com/s/dvn5k7d8bh3d43q/Department_KPIs.accdb?dl=0 

What I need to do is to get the Value from the KPI data into a matrix filtered by the KPI measures Division code by date and Metric_Code. For example:

                            Date             Date            ............

                            3/14/2020   3/21/2020

Division ESD         157             157               ............

              FMS        227             227             ............

These would be for the "ActCnt" in the Metic_Code column.

Here is a measure I made that works for a stacked bar chart but not the matrix since it's summed:

ActCnt = 
CALCULATE(
	SUM('qry_Employees'[SumOfValue]),
	'qry_Employees'[Metric_Code] IN { "ActCnt" }
)

I've tried using the FILTER function but just can't seem to get it right. Any help would be greatly appreciated.

[rbartlejr]

It comes up with an error for 'qry_Employees'[Division]. It says "Multiple arguments are not allowed in the ALLSELECTED function when the first argument is a table reference".

[themistoklis]

@rbartlejr 

Try either this: 

ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLSELECTED('qry_Employees'[Date], 'qry_Employees'[Division]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)

Or this

ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLSELECTED('qry_Employees'),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)

[rbartlejr]

Neither one works. The first gives an error and the second gives me the 20K total again.

[themistoklis]

best if you send a sample file to us... mask any sensitive data..

also it would be great if you provide the correct output on a table

[rbartlejr]

There is a link to the data in the original post. It has the Access database with all of the tables and queries. No sensitive information is included. As for what I'd like to see it would look like this:

                            Date             Date            ............

                            3/14/2020   3/21/2020

Division ESD         157             157               ............

              FMS        227             227             ............

[themistoklis · Accepted solution]

@rbartlejr 

Could you please have a look at the following link and let me know if this is what you would like to see?

https://www.dropbox.com/s/lovggqivwownchj/PowerBI_data.pbix?dl=0

[rbartlejr]

Exactly what you have. Thanks for the assistance.

[themistoklis]

@rbartlejr 

Can you try the following formula:

ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLEXCEPT('qry_Employees', 'qry_Employees'[Date]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)

[rbartlejr]

Sorry,

It doesn't give the correct value. It comes up as 20295 for each week and division.

[themistoklis]

@rbartlejr 

I think i used the wrong function. Try the following

ActCnt =
CALCULATE (
SUM('qry_Employees'[SumOfValue]),
FILTER (
ALLSELECTED('qry_Employees'[Date], 'qry_Employees'[Division]),
'qry_Employees'[Metric_Code] IN { "ActCnt" }
)
)