Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
How do i summarize the title and result in a matrix and count it, in excel, it would be a simple countif.
Example, talbe below:
Week1 | Week2 | Week3 |
A | A | A |
A | A | A |
A | A | A |
A | A | A |
A | A | A |
A | A | A |
A | A | A |
A | A | A |
A | A | B |
A | A | B |
A | A | B |
A | A | B |
A | A | B |
A | A | B |
A | B | B |
A | B | B |
A | B | B |
A | B | B |
A | B | B |
A | B | B |
A | B | C |
Need result to be:
Week1 | Week2 | Week3 | |
A | 21 | 14 | 8 |
B | 7 | 12 | |
C | 1 |
Thank you
Solved! Go to Solution.
You can just do a reference to the table and create a copy in power query. Then you can do all kinds of pivots without ruining your original table.
The other solution i had in mind is creating a small table:
A |
B |
C |
Then create a relationship between this column and each of your week columns. The relationships should not be active.
Finally create measures, along this lines of this:
Week1 = Calculate(countrows([Week1]) ; USERELATIONSHIP( [NewColumn] ; Week1))
Week2 = Calculate(countrows([Week2]) ; USERELATIONSHIP( [NewColumn] ; Week2))
etc...
Finally use the A/B/C column in the new table as a legend or row dimension with the new measures and you should be all set.
I'm going on lunch but let me know if you need any help!
/ J
Alright, never mind my previous message. Heres the columns:
week1 times =
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week1] = cat )
week2 times =
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week2] = cat )
week3 times =
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week3] = cat )
/ J
Thanks both, however, the data for A, B and C is done after the table have already been imported into powerbi.
Data gets imported, formula done in 'Data' to get the result of A, B and C. I cannot unpivot in power query if the data does not exist. I done it this way because A, B and C depends on other columns to get to that result.
The best way to solve this is to produce those column in power query if that is possible. Can you post the code you use to calculate the columns?
If it's impossible there is a solution that i have in mind but it's not a nice one 🙂
/ J
Thanks for your help. To get the columns result, its just an IF formula. i.e. for week 1, IF (date in file opened is less than today(), result is A etc.
I can do it in power query but then wouldn't want to unpivot as am using other columns in table to produce results and graphs. I guess i can just duplicate the table and then add it in power query.
Would like to know your possible but not nice solution though 🙂
You can just do a reference to the table and create a copy in power query. Then you can do all kinds of pivots without ruining your original table.
The other solution i had in mind is creating a small table:
A |
B |
C |
Then create a relationship between this column and each of your week columns. The relationships should not be active.
Finally create measures, along this lines of this:
Week1 = Calculate(countrows([Week1]) ; USERELATIONSHIP( [NewColumn] ; Week1))
Week2 = Calculate(countrows([Week2]) ; USERELATIONSHIP( [NewColumn] ; Week2))
etc...
Finally use the A/B/C column in the new table as a legend or row dimension with the new measures and you should be all set.
I'm going on lunch but let me know if you need any help!
/ J
Worked great, thanks very much.
Hi tex628
I now have another issue relating to the solution, didn't want to create another thread so thought i would ask in here.
In the new small table, for each category A, B & C, there are unit times next to it
Category | Unit Time |
A | 1 |
B | 2 |
C | 3 |
I have the calculated measure as per your solution so now all I want to do is multiply that measure week to the unit time. First one works fine when i put in (= unit time * week1) but then when i create another column for week 2 (= unit time * week2), it complains of a circular reference.
Would you know a solution to this?
Thanks
Just making sure im on the same page,
This is what we have now, more or less:
Category | Unit Time Week 1 | Unit Time Week 2 | Unit Time Week 3 |
A | 1 | 4 | 7 |
B | 2 | 5 | 8 |
C | 3 | 6 | 9 |
And the measures [Week1], [Week2] & [Week3].
Generally thinking i believe this should work:
[Week1] * Table[Unit Time Week1]
[Week2] * Table[Unit Time Week2]
[Week3] * Table[Unit Time Week3]
Something along the lines of this:
Week1 = Calculate(countrows([Week1]) ; USERELATIONSHIP( [NewColumn] ; Week1)) * SELECTEDVALUE(Table[Unit Time Week 1])
Let me know how it goes.
/ J
Hi, i have attached the example below in onedrive
https://1drv.ms/u/s!AvR5HzU6KTFHjzB36pZthOULapqe?e=grVqcg
If you load it up and go to 'Data' and look at table 2, the week2 times is giving a cicular reference.
Alright, never mind my previous message. Heres the columns:
week1 times =
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week1] = cat )
week2 times =
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week2] = cat )
week3 times =
VAR cat = Table2[Result]
VAR UT = Table2[Unit Time]
return
CALCULATE(COUNTROWS(Table1) * UT;Table1[Week3] = cat )
/ J
Wow, amazing. Thanks very much
Implementing it in the matrix is quite simple:
Week1 = CALCULATE(COUNTROWS(Table1) * SELECTEDVALUE( Table2[Unit Time] );USERELATIONSHIP(Table2[Result];Table1[Week1]))
Week2 = CALCULATE(COUNTROWS(Table1) * SELECTEDVALUE( Table2[Unit Time] );USERELATIONSHIP(Table2[Result];Table1[Week2]))
Week3 = CALCULATE(COUNTROWS(Table1) * SELECTEDVALUE( Table2[Unit Time] );USERELATIONSHIP(Table2[Result];Table1[Week3]))
On the other hand, producing the same numbers as calculated columns doesn't seem to be quite as easy. There's apparently an issue with creating several columns using different USERELATION() calculations. I'm going to be honest and say that i don't really know why this is the case.
If it's necessary to get the values in calculated columns we might need to look at a different solution. But i'm going to take another look and see if i can get it to work.
/J
Thanks, will give this a try and report back, didn't know i could calculate via other relationships as i had a similar idea but couldn't work out how to do it.
Starting table:
Highlight all columns and press "Unpivot":
Then press "Group By":
Use these settings:
Finally highlight "Attribute" column and press "Pivot":
Result:
/J
@TT1 , unpivot this data and create week a dimension table and then you create this easily
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |