Forum Discussion
bin matrix totals
Hi,
I have table with 3 columns. From this I created Matrix. Can't seem to get count of how many times 1,2 and 3 shows up on the matrix. For example, 1 shows up 4 times, 2 shows up 5 times and 3 shows up 2 times. Trying to show like below on Power bi report. Thanks for help!
https://drive.google.com/file/d/1r-x3Dtsf9hp1PkEqI8ejwKNa15V_ZxwV/view?usp=sharing
| # of records from matrix | Users with this count |
| 1 | 4 |
| 2 | 5 |
| 3 | 2 |
IDCompanyUser
|
Hi seth_chan ,
Please have a try.
Create a new table include 1,2,3.
Then create measures and columns.
Column= CALCULATE(COUNT(Table1[Company]),FILTER((Table1),Table1[Company]=EARLIER(Table1[Company])&&Table1[User]=EARLIER(Table1[User])))Measure = CALCULATE(COUNT(Table1[User]),FILTER(ALL(Table1),Table1[User]=SELECTEDVALUE(Table1[User])&&Table1[Company]<>SELECTEDVALUE(Table1[Company])&&Table1[Column]=SELECTEDVALUE(Table1[Column])))result = var _1= CALCULATE(distinctCOUNT(Table1[User]),FILTER(ALL('Table1'),Table1[Column]=SELECTEDVALUE('Table'[times]))) var _2=CALCULATE(MAX(Table1[Column]),FILTER(ALL(Table1),[Measure]<>BLANK())) return IF(MAX('Table'[times])=_2,_1+[Measure]+1,IF(ISBLANK([Measure]),_1,BLANK()))Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi seth_chan ,
Please refer to my pbix file to see if it helps you.
Create a measure.
Measure = CALCULATE ( COUNT ( Table1[User] ), FILTER ( ALL ( Table1 ), Table1[Company] = SELECTEDVALUE ( Table1[Company] ) && Table1[User] = SELECTEDVALUE ( Table1[User] ) ) )If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Polly,
Thanks for reply. The result I wanted to get from powerbi was to show the report like below.
so looking at the matrix report which shows the Company, User and ID (ID shows the count of times the User shows up for a Company, ie... Bob shows up twice for both Microsoft and Google, Andy shows up twice for Google, Frank and Bill shows up twice for Apple so I want to show 5 for the times that Users showed up 2 times, 4 for the times that Users showed up 1 time (Joe, Null, Kris and Jack) and 2 for the times that Users showed up 3 times (Andy and Mike). So I would need some kind of calculation to bring in the # of times a User shows up for a company 1 times, 2 times and 3 times in my example and then another calculation that counts the occurance of instances. Hope that makes sense... Thanks for your help!
Hi seth_chan ,
Please have a try.
Create a new table include 1,2,3.
Then create measures and columns.
Column= CALCULATE(COUNT(Table1[Company]),FILTER((Table1),Table1[Company]=EARLIER(Table1[Company])&&Table1[User]=EARLIER(Table1[User])))Measure = CALCULATE(COUNT(Table1[User]),FILTER(ALL(Table1),Table1[User]=SELECTEDVALUE(Table1[User])&&Table1[Company]<>SELECTEDVALUE(Table1[Company])&&Table1[Column]=SELECTEDVALUE(Table1[Column])))result = var _1= CALCULATE(distinctCOUNT(Table1[User]),FILTER(ALL('Table1'),Table1[Column]=SELECTEDVALUE('Table'[times]))) var _2=CALCULATE(MAX(Table1[Column]),FILTER(ALL(Table1),[Measure]<>BLANK())) return IF(MAX('Table'[times])=_2,_1+[Measure]+1,IF(ISBLANK([Measure]),_1,BLANK()))Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.appreciate the help v-polly-msft!
I have created conditional column for age bins in month & then year. Now i need to rearrange the same . like <3M,3M-6M,6M- 12M, 1yrs-2Yrs........
Please help in same
