Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I am new to PowerBI and need your help in calculting the below one's.
The calculations are:
For Private Sector - # of 'Private Sector' Proposals Awarded /# of 'Private Sector' Proposals Awarded + # of 'Private Sector' Proposals Rejected + # of 'Private Sector' Proposals Cancelled (%)
For USG - # of 'USG' Proposals Awarded /# of USG' Proposals Awarded + # of 'USG' Proposals Rejected + # of 'USG' Proposals Cancelled (%)
For Total (Column) - # of 'Private Sector + USG' Proposals Awarded /# of 'Private Sector + USG' Proposals Awarded + # of 'Private Sector + USG' Proposals Rejected + # of 'Private Sector + USG' Proposals Cancelled (%)
For Total (Row) - Total # Proposals Awarded (%)
Proposas (Row) - total count of Proposals for Private Sector, USG (whole number)
#Proposals awarded means - Status = won and Status Reason = Won
#Proposals Rejected mean - Status = Won, Lost and Status Reason = Out-Sold
#Proposals Canceled means - Status = Won, Lost and Status Reason = Canceled
Private Sector and USG are values in Sector field.
Now I need to show the count of Proposals (type of Proposal) for every Business Line(Line of Business) based on the above calculations.
if business Line has "Diaspora Engagement" value then i should get the count of proposals based on the above calculations in % for individual Business line.
Can anyone please help me in this. Please
Thanks in Advance!!!
Solved! Go to Solution.
@Pravallika_L
Please try
M_PS =
DIVIDE (
[# of Proposals Awarded_PS],
CALCULATE ( [# of Proposals_WCO_PS], ALLSELECTED ( v_dyn_opportunity ) )
)
Hi @tamerj1 ,
I want to make the entire #Proposals row background as white and in BOLD and if any of the business lines are blank in Private sector and USG it also must have white background. And if there is data for any of the business lines in Private Sector and USG, then i should apply color coding for that background like if the Private Sectoris in the range :
0% to 20% - "#F16D3D"
21% t0 30% - "#F18C67"
31% to 40% - "#F4B663"
41% to 60% - "#F6DB71"
61% to 70% - "#D9DDA3"
71% to 80% - "#C0D784"
81% to 90% - "#A0D075"
91% to 100% - "#8BB862"
And USG has another different set of color codes for Background
you may place business line at the rows and the sector at the columns of the matrix then place the following measure in the values
DIVIDE (
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Status] = "Won"
),
COUNTROWS ( 'Table' )
)
Hi tamerj1,
Thanks for the reply.
I have already placed the Business line in rows and sector in columns. But i have took different measures to calculate the private sector and USG as below given only for Private Sector just to get the correct output: (All are measures only)
1. # of Proposals Awarded_PS = CALCULATE(COUNT(v_dyn_opportunity[Lines_of_Business]),filter(v_dyn_opportunity,v_dyn_opportunity[Sector] = "Private Sector"),filter(v_dyn_opportunity,v_dyn_opportunity[Status] = "Won"),filter(v_dyn_opportunity,v_dyn_opportunity[Status_Reason] = "Won"))
In the above screenshot, the value which is nothing but count of rows for a particular business line 1 must be divided by with the total count of proposals 3 and get the output as 0.33 in M_PS thats the formula i have written but it's not showing 0.33 instead i am seeing 1.00. But in the total i am able to see 0.67 which is sum of 0.33 and o.33. I am not able to see these values
@Pravallika_L
Please try
M_PS =
DIVIDE (
[# of Proposals Awarded_PS],
CALCULATE ( [# of Proposals_WCO_PS], ALLSELECTED ( v_dyn_opportunity ) )
)
Hi tamerj1,
Thankyou So much....... It worked!!!!
Also can you please help me with the Row Total and Column total and there is another row with Proposals. I am not getting how i should change the calculations for Total column and total row and how to add another row with #proposals? i have mentioned in the first post
@Pravallika_L
The total number of proposals is just the denominator
# of Proposals =
CALCULATE ( [# of Proposals_WCO_PS], ALLSELECTED ( v_dyn_opportunity ) )
Hi tamerj1,
I need to know how i can provide calculation for the totals as they are inbuilt and not user editable and also the grand total
Do you got me? also i am not getting the total for the first row and grand total in the below table. These totals appears directly in matrix
ALso the one in the last row #Proposals how should i add that after the total?
@Pravallika_L
Please try
=
SUMX (
SUMMARIZE ( v_dyn_opportunity[Lines_of_Business], v_dyn_opportunity[Sector] ),
[Your Measure]
)
what Measure should I add in the place of [Your Measure]?
@Pravallika_L
The measure that you are using in this matrix. Then place the new measure instead.
Sorry i am not getting what you are trying to convey. I have used the below measure for calculating the column total
for total column -
for total row -
and # proposals row -
and the grand total
Hi,
When can we connect please?
Now
Sure. But i don't have personal id in teams. Can you send me an invite?
Hi,
I have mailed you the data and the formulas.
Thankyou
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |