Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi PBI'ers,
I'm stumped , several hours later 😞 how do I do the below in DAX:
Solved: Join/Pivot two tables with no relations? - Microsoft Power BI Community
Ideally there would be a row for each "date" and "name" in their respective columns instead.
Solved! Go to Solution.
Hi, @MosheLP
According to your description and sample test data, I can now roughly understand your requirement, but I still think the percentage value for the May HR is incorrect because the HR department only has two employees in your test data, it should be 50% am I right?
Based on your logic, I figured out a method to achieve this, you can follow my steps:
Create a calculated column in the table ‘Product consumption’ to get the month number:
Month number =
var _number=
SWITCH([MONTH],"August",08,"December",12,"July",07,"November",11,"October",10,"September",09,"April",04,"February",02,"January",01,"March",03,"May",05,"June",06)
return
VALUE(_number)
Then create two measures like this:
Distinct Employees =
CALCULATE(DISTINCTCOUNT('Product consumption'[Employee Name]),FILTER(ALL('Product consumption'),[Month number]<=MAX('Product consumption'[Month number])&&[Month number]>=MAX('Product consumption'[Month number])-2&&[Department Name]=MAX('Product consumption'[Department Name])))
Percentage =
var _count=COUNTX(FILTER(ALL('People Data Table'),[Department Name]=MAX('Product consumption'[Department Name])),[Employee Name])
return
divide([Distinct Employees],_count)
Then set the measure format of [Percentage] to percentage:
Then create a table chart and place it like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @MosheLP
According to your description and sample test data, I can now roughly understand your requirement, but I still think the percentage value for the May HR is incorrect because the HR department only has two employees in your test data, it should be 50% am I right?
Based on your logic, I figured out a method to achieve this, you can follow my steps:
Create a calculated column in the table ‘Product consumption’ to get the month number:
Month number =
var _number=
SWITCH([MONTH],"August",08,"December",12,"July",07,"November",11,"October",10,"September",09,"April",04,"February",02,"January",01,"March",03,"May",05,"June",06)
return
VALUE(_number)
Then create two measures like this:
Distinct Employees =
CALCULATE(DISTINCTCOUNT('Product consumption'[Employee Name]),FILTER(ALL('Product consumption'),[Month number]<=MAX('Product consumption'[Month number])&&[Month number]>=MAX('Product consumption'[Month number])-2&&[Department Name]=MAX('Product consumption'[Department Name])))
Percentage =
var _count=COUNTX(FILTER(ALL('People Data Table'),[Department Name]=MAX('Product consumption'[Department Name])),[Employee Name])
return
divide([Distinct Employees],_count)
Then set the measure format of [Percentage] to percentage:
Then create a table chart and place it like this:
And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @MosheLP
According to your description, I can roughly understand your requirement, but I still can’t understand the logic of your two columns [distinct employees] and [Percentage].
You said that there are 3 employees in for IT in August as Mark, Harry, and Tom, but for HR why there is only 1 employee in? Henry and Sam are in in the recent 3 months based on your logic.
For the column [Percentage], would you like to explain the denominator of it in detail?
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi and thank you, you are correct, sadly I am doing this manually.
It should look like:
Period | Department | Distinct Employees | Percentage |
May | Finance | 1 | 33% |
May | HR | 1 | 33% |
May | IT | 1 | 25% |
June | IT | 2 | 50% |
July | HR | 2 | 100% |
July | IT | 3 | 75% |
July | Finance | 2 | 67% |
August | IT | 3 | 75% |
August | HR | 2 | 100% |
August | Finance | 2 | 67% |
The denominator is the total employees in the department.
There's not a nice way to pivot with DAX. You have to define a new column manually for each one you want. See here for example: https://community.powerbi.com/t5/Quick-Measures-Gallery/DAX-Unpivot/td-p/574832
On the other hand, you very rarely actually need to do this in order to achieve your ultimate goal since DAX handles unpivoted data nicely. What's the reasoning behind doing this? Are you after a certain visual or calculation or what? Are you actually looking for the pivoted shape or is an unpivoted crossjoin that you can then pivot in a matrix visual OK too?
Thanks for your reply, much appreciated.
My goal is to create a bar race chart, requiring me to show clusters of users' (e.g. employees in a Department) product consumption percentage over a period of months i.e. which department has the highest product consumption percentage from it's employees.
So i have two tables, one with the users in their clusters and the second table being the product consumption by user for those who have consumed a product.
Therefor my intention is to aggregate the purchases by department into a new table so as to obtain the percentage value for product consumption in a department. After which I will then need to accrue(running total) the product consumption aggregate for each department by month.
I have added an excel e.g that can hopefully explain better:
This is definitely doable. If you share a file or something I can copy & paste from, I can probably help but I'm not keen to retype data from a screenshot.
Please see below:
People Data Table |
Employee Name | Department Name |
John | Finance |
Mary | Finance |
Diana | Finance |
Mark | IT |
Tom | IT |
Harry | IT |
Sandra | IT |
Sam | HR |
Henry | HR |
Product consumption | |||
Product Name | Employee Name | Department Name | Month |
MS Word | John | Finance | May |
PowerPoint | Henry | HR | May |
Excel | Mark | IT | May |
Outlook | Mark | IT | June |
MS Word | Harry | IT | June |
PowerPoint | Sam | HR | July |
PowerPoint | Harry | IT | July |
Excel | Henry | HR | July |
Outlook | Mary | Finance | July |
Excel | Tom | IT | August |
MS Word | Henry | HR | August |
Desired Result | |||
Period | Department | distinct employees | Percentage |
May | Finance | 1 | 33% |
May | HR | 1 | 33% |
May | IT | 1 | 25% |
June | IT | 2 | 50% |
July | HR | 1 | 50% |
July | IT | 3 | 75% |
July | Finance | 2 | 67% |
August | IT | 3 | 75% |
August | HR | 1 | 50% |
Hope this helps, and thank you in advance.
I don't understand where some of your numbers are coming from (e.g. how can there be a count of 3 in August when there are only two rows?) but I think something like this should get you closer to what you're after:
Desired =
ADDCOLUMNS (
SUMMARIZE (
'Product',
'Product'[Month],
'Product'[Department Name],
"Distinct Employees", DISTINCTCOUNT ( 'Product'[Employee Name] )
),
"Percentage",
DIVIDE (
[Distinct Employees],
COUNTROWS (
FILTER (
People,
People[Department Name] = EARLIER ( 'Product'[Department Name] )
)
)
)
)
Hi Alexis,
I gave it a test and it's almost there.
This missing part is just the values accruing which is already done in the percentage column as an example. So I realise that the EARLIER function has large to play in this but I have had no luck since my tests.
I also realise the results table can be somewhat confusing as I manually entered the results I am looking to achieve. Each month that follows is meant to take into account activity from the previous one meaning August should also include Finance in it's running total together with the accrued distinct employees for it's department.
That is why there are 3 employees in for IT in August as Mark, Harry and Tom consumed their products over these three months. I hope that clears up the confusion and I am sincerely grateful.
If you kindly can refer to my results table
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |