Help: Double Counting Issue in Column Chart

[styburd]

Hello, I am trying to create a column chart to show the dollar value of commitments that were made each year to investment managers, but my data is too granular and results in double counting. (I need to keep the granularity for other parts of the dashboard).

For example in the picture below, there are three unique funds that commitments were made to, and each fund has three portfolio companies. For my chart I have vintage year on the x-axis and the total commitment on the y-axis. The value that I want to have would be $30m ($10m committed to fund A and $20m committed to fund B) in 2020 and $15m in 2021. The problem is that the program is summing the entire commitment column based on the vintage year and producing $90m for 2020 and $45m for 2021, is there a way to make the sum not double count and only sum unique fund names in each vintage year?

[Mikelytics · Accepted solution]

Hi @styburd 

Please try the following:

Disclaimer: only works when the total commitment per Fund per Year is everytime the same like in your table.  A is everytime 10.0000, B is20.000 and c is 15.000

Base Table

Measure

Fund Measure = 

var var_Summarized_Table = 
    SUMMARIZE('Table',
        'Table'[Fund Name],'Table'[Vintage Year],"Total Commitment",MAX('Table'[Total Commitment]))

RETURN
    SUMX(
        var_Summarized_Table,
        [Total Commitment]
    )

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn