Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
seth_chan
Frequent Visitor

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 matrixUsers with this count
14
25
32

seth_chan_0-1656517967722.png

 

IDCompanyUser
1MicrosoftAndy
2MicrosoftAndy
3MicrosoftAndy
4MicrosoftBob
5MicrosoftBob
6MicrosoftJoe
7Microsoft 
8GoogleAndy
9GoogleAndy
10GoogleBob
11GoogleBob
12AppleFrank
13AppleFrank
14AppleBill
15AppleBill
16MetaKris
17MetaJack
18MetaMike
19MetaMike
20MetaMike
1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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()))

vpollymsft_0-1657088156929.png

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.

View solution in original post

5 REPLIES 5
gresanjay
New Member

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 samePicture1.png

v-rongtiep-msft
Community Support
Community Support

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()))

vpollymsft_0-1657088156929.png

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!  

v-rongtiep-msft
Community Support
Community Support

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] )
    )
)

vpollymsft_0-1656903754908.png

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!

seth_chan_0-1657021348843.png

 

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.