Forum Discussion
Creating a custom column
Hello, sorry for the missmatch
The easiest way to achieve that in PQ, would be to create a summarized table and join your two tables
To achieve that, make a right click on the table and select reference (it will create a shortcut of the source table)
Then, in our new table, go in home and click on group by and in advanced, add a max column and a min column
Now, you have the desired table, in this table, add a calculated column to make the difference between max and min
Then go back in your main table, and click on merge queries (create a new query) and select dpt for both table
Now, extend the desired column and you have your desired table
Just for the two tables you use at the begining, make a right click on it and untick enable load (just to avoid too see these tables in your semantic model)
Thank you!😊 This worked
Hi Timahake8
Your request is not very clear, you need the max for? A departement? A staff member ?
If you need to achieve this kind of measure for dpt, this is the result
For this result, you have to create 3 measure:
CALCULATE(
MAX('YourTable'[% Pen]),
ALLEXCEPT('YourTable', 'YourTable'[Department])
)
CALCULATE(
MIN('YourTable'[% Pen]),
ALLEXCEPT('YourTable', 'YourTable'[Department])
)
Or do you just need to get 67% everywhere in Max% pen, 3% in Min% pen and the difference (64) in the last column?
Now, if you need these values, you just have to create one column for max:
Max %Pen =
MAX('YourTable'[% Pen])
Min %Pen =
MIN('YourTable'[% Pen])
Diff %Pen = Max %Pen - Min %Pen
If you try to achieve something else, do not hesitate to ask us 😉
Hi, many thanks. I am trying to achieve the first solution you have explained, but within power query rather using a dax. Thank you
Hello, sorry for the missmatch
The easiest way to achieve that in PQ, would be to create a summarized table and join your two tables
To achieve that, make a right click on the table and select reference (it will create a shortcut of the source table)
Then, in our new table, go in home and click on group by and in advanced, add a max column and a min column
Now, you have the desired table, in this table, add a calculated column to make the difference between max and min
Then go back in your main table, and click on merge queries (create a new query) and select dpt for both table
Now, extend the desired column and you have your desired table
Just for the two tables you use at the begining, make a right click on it and untick enable load (just to avoid too see these tables in your semantic model)
Thank you!😊 This worked