Forum Discussion
Dynamic Total Count Percentage
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?
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.
3 Replies
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.
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
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.
