Count Problem

[Anonymous]

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: 

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: 

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: 

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: 

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!

[Ashish_Mathur]

Hi,

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

[mahoneypat · Accepted solution]

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

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

Regards,

Pat

[Anonymous]

@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:

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

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

[mahoneypat]

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.

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

Regards,

Pat

[Anonymous]

@mahoneypat ,

You are a life-saver.

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

[Anonymous]

@mahoneypat 

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