How to show different values in Matrix using filters from slicer

[Depm]

Hi All,

I've my data as below

Month	Client	Team A # of projects	Team B # of projects	Team A # of hits	Team B # of hits	Team A # of Misses	Team B # of Misses
Jan	XXX	1	3	1	2	3	4
Jan	YYY	2	4	1	2	3	4
Feb	XXX	1	3	1	2	3	4
Feb	YYY	2	4	1	2	3	4
Mar	XXX	1	3	1	2	3	4
Mar	YYY	2	4	1	2	3	4

I need couple of visuals on the report i.e. slicer with option "Overall", "Team A" and "Team B"

based on the value selected from slicer, below is how i want the matrix to be populated.

	Overall
		Jan	Feb	Mar
XXX	# of projects	4	4	4
	# of hits	3	3	3
	# of misses	7	7	7
YYY	# of projects	6	6	6
	# of hits	3	3	3
	# of misses	7	7	7
	Team A
		Jan	Feb	Mar
XXX	# of projects	1	1	1
	# of hits	1	1	1
	# of misses	3	3	3
YYY	# of projects	2	2	2
	# of hits	1	1	1
	# of misses	3	3	3
	Team B
		Jan	Feb	Mar
XXX	# of projects	3	3	3
	# of hits	2	2	2
	# of misses	4	4	4
YYY	# of projects	4	4	4
	# of hits	2	2	2
	# of misses	4	4	4

Please help me if there is any way i can create a seperate table using DAX so i can show data with different cuts in one singlt matrix with help of slicer. 

Thanks,

Deepak

[v-zhangti · Accepted solution]

Hi, @Depm 

The second method is to create a new table in Desktop using Dax.

New table = 
Var _table1=SUMMARIZE('Table','Table'[Month],'Table'[Client],"Team","Team A","Type"," # of projects","Value",SUM('Table'[Team A # of projects]))
Var _table2=SUMMARIZE('Table','Table'[Month],'Table'[Client],"Team","Team B","Type"," # of projects","Value",SUM('Table'[Team B # of projects]))
Var _table3=SUMMARIZE('Table','Table'[Month],'Table'[Client],"Team","Team A","Type"," # of hits","Value",SUM('Table'[Team A # of hits]))
Var _table4=SUMMARIZE('Table','Table'[Month],'Table'[Client],"Team","Team B","Type"," # of hits","Value",SUM('Table'[Team B # of hits]))
Var _table5=SUMMARIZE('Table','Table'[Month],'Table'[Client],"Team","Team A","Type"," # of Misses","Value",SUM('Table'[Team A # of Misses]))
Var _table6=SUMMARIZE('Table','Table'[Month],'Table'[Client],"Team","Team B","Type"," # of Misses","Value",SUM('Table'[Team B # of Misses]))
Return
UNION(_table1,_table2,_table3,_table4,_table5,_table6)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[v-zhangti]

Hi, @Depm 

You can try the following methods. The first method in Power Query, Check the first two columns and select Unpivot Other Columns:

Step1:

Then:

Step2:

Then:

Then in the Power BI Desktop:

Overall:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[parry2k]

@Depm it is easier to unpivot the data and then you can visualize whatever the way you want, to test, start a new blank query -> advanced editor and paste the following M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRioiIAJKGQGwMpY2gbBOlWB2YqsjISKiMCVZVbqlJRJgFUUXILN/EIiLMgqjCa1YsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Client = _t, #"Team A # of projects" = _t, #"Team B # of projects" = _t, #"Team A # of hits" = _t, #"Team B # of hits" = _t, #"Team A # of Misses" = _t, #"Team B # of Misses" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Client", type text}, {"Team A # of projects", Int64.Type}, {"Team B # of projects", Int64.Type}, {"Team A # of hits", Int64.Type}, {"Team B # of hits", Int64.Type}, {"Team A # of Misses", Int64.Type}, {"Team B # of Misses", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month", "Client"}, "Attribute", "Value"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Other Columns", "Team", each Text.BeforeDelimiter([Attribute], " ", 1), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Type", each Text.AfterDelimiter([Attribute], " ", 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Attribute"})
in
    #"Removed Columns"

close and apply. Now you can use a matrix visual to see the data the way you want, and you can have a slicer on Team to filter on individual teams or see data for all the Teams together.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[Depm]

Thanks you for providing the query syntax. I was wondering if this is possible using DAX? If yes, can someone please share the DAX syntax for the same problem.