Forum Discussion
Grouping columns based on field parameters
Hi, hoping you can help crack this one as driving me mad!
We're trying to create a table that shows sales and volume by different time periods. The time periods are setup within a field parameter so that the user can select Current Week and it will return Sales Current Week, Sales Current Week vs Previous %, Sales Current Week vs Last Year %, Volume Current Week, Volume Current Week vs Previous %, Volume Current Week vs Last Year %. To avoid duplication of names and make the view more user friendly we've shortened the names in the field parameter DAX.
Sales and Volume parameter = {
("Current Week", NAMEOF('Measure'[Sales Current Week]), 3, "Sales", "Current Week"),
("vs Previous %", NAMEOF('Measure'[Sales Current Week vs Last Week Progressive %]), 4, "Sales", "Current Week"),
("vs Last Year %", NAMEOF('Measure'[Sales Current Week vs Last Year Progressive %]), 5, "Sales", "Current Week"),
("Current Week", NAMEOF('Measure'[Volume Current Week]), 33, "Volume", "Current Week"),
("vs Previous %", NAMEOF('Measure'[Volume Current Week vs Last Week Progressive %]), 34, "Volume", "Current Week"),
("vs Last Year %", NAMEOF('Measure'[Volume Current Week vs Last Year Progressive %]), 35, "Volume", "Current Week"),
}
The issue comes when we try to add a grouping to the matrix so that we can see Sales / Volume headers as below (imagine top cells are merged across the three columns):
| Sales | Volume | |||||
| Current Week | vs Previous % | vs Last Year % | Current Week | vs Previous % | vs Last Year % | |
| Category 1 | ||||||
| Category 2 |
We have tried adding the mapping to Sales/Volume as an extra column in the field parameter DAX (see above) and also by using the grouping function but both methods seem to result in all measures being duplicated under both Sales and Volume. Same thing with creating a separate SWITCH measure so now I'm running out of ideas. We could put a text box above the table but the columns change width based on the time period selection (even when disabling auto-size).
Before any one suggests calculation groups, we originally had it setup that way but the users didn't like the groupings by time period and custom formatting doesn't seem to work using a combination of field parameters and calculation groups!
Thanks,
Laura
Hi Laura,
Instead of using Field Parameters. You can achieve the same view in the matrix visual using disconnected table and switch measures.
1. First you should be creating a disconnected table like below.
Matrix Table:
Header Sub Header Sales Current Week Sales vs Previous % Sales vs Last Years % Volume Current Week Volume vs Previous % Volume vs Last Years % 2. Create three switch measures for Current Week , vs Previous % and vs Last Years %.
Eg:Current Week Switch =SWITCH(TRUE(),SELECTEDVALUE(Matrix[Sub Header]) = "Current Week" && SELECTEDVALUE(Matrix[Header]) = "Sales", [Sales Current Week],SELECTEDVALUE(Matrix[Sub Header]) = "Current Week" && SELECTEDVALUE(Matrix[Header]) = "Volume",[Volume Current Week])3. Create other two measures for vs Previous % and vs Last Year %4. Now you can pull in the Header and Sub header column from disconnected table into the Column section of the matrix, Rows from your fact / dim table and Values from three switch measures which you will be creating.Thanks,Jai 🙂
1 Reply
Hi Laura,
Instead of using Field Parameters. You can achieve the same view in the matrix visual using disconnected table and switch measures.
1. First you should be creating a disconnected table like below.
Matrix Table:
Header Sub Header Sales Current Week Sales vs Previous % Sales vs Last Years % Volume Current Week Volume vs Previous % Volume vs Last Years % 2. Create three switch measures for Current Week , vs Previous % and vs Last Years %.
Eg:Current Week Switch =SWITCH(TRUE(),SELECTEDVALUE(Matrix[Sub Header]) = "Current Week" && SELECTEDVALUE(Matrix[Header]) = "Sales", [Sales Current Week],SELECTEDVALUE(Matrix[Sub Header]) = "Current Week" && SELECTEDVALUE(Matrix[Header]) = "Volume",[Volume Current Week])3. Create other two measures for vs Previous % and vs Last Year %4. Now you can pull in the Header and Sub header column from disconnected table into the Column section of the matrix, Rows from your fact / dim table and Values from three switch measures which you will be creating.Thanks,Jai 🙂