Dynamic Total Count Percentage

[Sagar01]

Hello everyone,

I'm reaching out regarding the provided matrix, where I'm aiming to calculate the percentage change in opportunities based on the total count of countries. This involves utilizing two interconnected tables: the Accounts table, which holds billing country data, and the Opportunities table, which contains information on opportunity status.

The columns used are Account[Billing Country] and Opportunity[Stage] in the row section, and Count of Opportunity[Stage] in the value section of the matrix.

To illustrate, for the "W0-Close Won" category, I aim to calculate the percentage by dividing 2 (representing the number of opportunities) by 11 (representing the total count of countries), resulting in 18.18%.

Could someone please assist me in achieving this?

Can someone plese help in achiving the same?

[Sagar01]

The sructure is like Accounts table is connected to Opportunity table in one to many relationship and we are getting Billing Country from accounts table

[Anonymous · Accepted solution]

Hi @Sagar01 

Please refer to the following solution.

1.Create a measure to calculate count of stage.

Total Opportunities= Count(Opportunity[Stage])

2. Create another measure to calculate %

% Opportunities =
VAR a =
    CALCULATE (
        [Total Opportunities],
        FILTER (
            ALLSELECTED ( Account ),
            [Billing Country] IN VALUES ( Account[Billing Country] )
        )
    )
RETURN
    DIVIDE ( [Total Opportunities], a )

Best Regards!

Yolo Zhu

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

Best Regards!

Yolo Zhu

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

[Anonymous]

Hi @Sagar01 

You can refer to the following measure.

1.Create a measure to calculate count of stage.

Total Opportunities= Count(Opportunity[Stage])

2. If your Opportunity table have the country column, if it exists,the following measure use the country column in  Opportunity table Create another measure to calculate %

% Opportunities =
VAR a =
    CALCULATE (
        [Total Opportunities],
        FILTER (
            ALLSELECTED ( Opportunity ),
            [Billing Country] IN VALUES ( Opportunity[Billing Country] )
        )
    )
RETURN
    DIVIDE ( [Total Opportunities], a )

Best Regards!

Yolo Zhu

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