cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Count Problem

Hi,

I do not know if this is possible, but I would really appreciate any help I can get. Here is the sample data set I will be using to help you understand my problem: 

cnormand_0-1594157092337.png

My goal is to compare a single fund's investors to the rest of the investors. I want to be able to show the repeat investors within different funds. To do this, I made a "count" calculation to help identify the investors. Here it is: 

Count =
IF (
    ISINSCOPE ( 'Table'[Investor] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Fund] ),
        ALL ( 'Table'[Fund] )
    ),
    CALCULATE (
        COUNT ( 'Table'[Fund] )
    )
)

Here is what I have now: 

asdfh4tb.png

I want to compare a single fund's investors (by using the left slicer) to the rest of the investors. This "count" calculation works perfectly, as the slicer does not affect the calculation at all. I can select a single fund and it will show me that fund's investors and how many times they repeat across the others: 

1.png

But now my problem is that I want to show what funds "Louis" (for example) is invested in. When I add the fund to the next level on the matrix, I get the following: 

2.png

It only shows me fund 3 for Louis (because the slicer is affecting it), but I want it to show all of the funds Louis is invested in.

 

I have spent so much time trying to figure this out, so any help at this point will be much appreciated.

 

Thank you in advance!

 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

To do this kind of thing, you need a separate and disconnected table for your fund list for the slicer.  Follows these steps to get the output shown below.

 

1.  Make a disconnected (no relationship) table called Funds

Funds = DISTINCT('Table'[Fund])
 
2.  Use it in your slicer.
 
3. Use the following two measures in your matrix visual
 
Selected Fund Count = CALCULATE(COUNTROWS('Table'), TREATAS(VALUES(Funds[Fund]), 'Table'[Fund]))
 
Fund Count = if(ISBLANK([Selected Fund Count]), BLANK(), COUNTROWS('Table'))
 
funds.png
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

Hi,

Maybe i am not clear about your requirement but why don't you just create a slicer of Investor and select Louis there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Super User IV
Super User IV

To do this kind of thing, you need a separate and disconnected table for your fund list for the slicer.  Follows these steps to get the output shown below.

 

1.  Make a disconnected (no relationship) table called Funds

Funds = DISTINCT('Table'[Fund])
 
2.  Use it in your slicer.
 
3. Use the following two measures in your matrix visual
 
Selected Fund Count = CALCULATE(COUNTROWS('Table'), TREATAS(VALUES(Funds[Fund]), 'Table'[Fund]))
 
Fund Count = if(ISBLANK([Selected Fund Count]), BLANK(), COUNTROWS('Table'))
 
funds.png
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Anonymous
Not applicable

@mahoneypat 

 

Also, for my real model, I have other values that need to be in the matrix. When I add those values into the matrix, all of the Investor Names pop up. Here is a more accurate depiction of my data:

cnormand_0-1596478088033.png

When I use your formulas, it works until I add committed capital and descriptions onto the values in the matrix:

cnormand_1-1596478382481.png

Please let me know if there is a way to solve this. Thank you.

 

You don't need to have that measure in the visual.  And to get your desired result (adding other measures w/o adding rows), you can use the Selected Fund Count measure to filter  the matrix visual.  Just highlight the matrix, and drag the measure into the Filters On This Visual in the Filters panel.  Set it to "Is Not Blank" and Apply Filter.

 

filtervisual.png

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat ,

 

You are a life-saver.

 

I have been working on this forever. Thank you so much. 

Anonymous
Not applicable

@mahoneypat 

 

Thank you so much. Do I need to have the "Selected Fund Count" in the matrix in order for this to work?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors