How to custom combine / group items in multiple rows use DAX

[lladna]

I think I am missing something simple, so I appreciate anyone that is able to help.  To start I would like to do this via DAX and not in PowerQuery; but could be swayed if that doesn't work.

I have a report that shows in the column the actual payroll paid, and this is itemized by each type of pay (i.e. Regular Salaries, Overtime, FICA, Medicare, etc.). 

Dataset itemized label	Pay
Salary	$20.00
Overtime	$50.00
FICA	$5.00
Medicare	$10.00
Total	$85.00

What I want to do is create groups of these items that sometimes overlap.  For example, I want one row to show all items above totaled together, another row to show all items minus overtime, and then another row to show FICA + Medicare. 

Custom Group	Pay
All Fields	$85.00
All Fields - OT	$35.00
Tax Fields	$15.00

In other words sometimes the rows will contain the same items; because of this I am not able to use the "groups" when you right click the item in fields and do "new group".

I want to then be able to use these in a matrix so they all appear on the same drill down command,  i.e.:

• Starts with Business Group (total of all salaries)
• Drill Down One is each employee (still total of all salaries)
• Drill Down Two is each of these categories that are created

Thanks in advance for any help you are able to provide!

--David

[GuustD]

Hi!

I don´t think you need to create new groups. What you need to do is create new measures;

Measure "All fields" = sum(pay)

Measure "All fields OT" = calculate([All fields], filter(tablename, itemized lable <>"Overtime"))

Measure "Tax fields" =  calculate([All fields], filter(tablename, itemized lable ="Fica")||filter(tablename, itemized lable ="Medicare")

Hope this helps. If so then vote this answer as the right one!

Kind regards,

Guust

[GuustD]

I see ythat the last measure misses a closing bracket so it should be;

Measure "Tax fields" =  calculate([All fields], filter(tablename, itemized lable ="Fica")||filter(tablename, itemized lable ="Medicare"))

[lladna]

Thanks very much.

Would I then be able to use these in a matrix so they all appear on the same drill down command  i.e. Starts with Business Group (total of all salaries), Drill Down One is each employee (still total of all salaries), and then finally Drill Down Two is each of these categories that are created.

Thanks again for your time.

[GuustD]

Hi!

No that will not be possible. I think there are 2 options here;

1. Create the measures as described earlier, to be able to show the numbers side by side as in your example (use the option "Show on rows" as described in the following thread;

https://datasavvy.me/2017/08/10/you-can-now-put-values-on-rows-in-power-bi/

2. Create a hierarchy with 2 levels. The first level is your total group (all other groups will fall under this group). The second level will consist of 3 groups ("Base", "Overtime", "Tax"). You can name the groups in whatever way you want, but your dataset would look something like this;

Dataset itemized label	Level 1	Level 2	Pay
Salary	Total	Base	$20.00
Overtime	Total	Overtime	$50.00
FICA	Total	Tax	$5.00
Medicare	Total	Tax	$10.00

You can create these groups using DAX (check the Switch function) and then create the hierarchies as you have been doing.

Hope this works!

Kind regards,

Guust