Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pravallika_L
Helper I
Helper I

Create a Matrix with % for individual types based on the count of categories

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!!!

 

1 ACCEPTED SOLUTION

@Pravallika_L 
Please try

M_PS =
DIVIDE (
    [# of Proposals Awarded_PS],
    CALCULATE ( [# of Proposals_WCO_PS], ALLSELECTED ( v_dyn_opportunity ) )
)

View solution in original post

17 REPLIES 17
Pravallika_L
Helper I
Helper I

Hi @tamerj1 ,

 
How can I provide the color code for blank ones? If there are blanks in the data for Business Lines, will it show 0% in the total?
 
Pravallika_L_2-1675259959048.png

 

 

Also as I am using sector fields directly in the column, I can't provide color coding in the conditional formatting tab for individual columns i.e., Private Sector must have one set of color codes and USG must have another set of color codes? How can I achieve this? And when i used COnditional formatting for background colors for the sector field, it's showing the color for #Proposals also like below
Pravallika_L_3-1675260205031.png      Pravallika_L_4-1675260269341.png

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

 
 
Please help me out.....
 
Thankyou!!!
tamerj1
Super User
Super User

Hi @Pravallika_L 

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

 
2. # of Proposals_WCO_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] in {"Won","Lost"}),filter(v_dyn_opportunity,v_dyn_opportunity[Status_Reason] in {"Won","Canceled","Out-Sold"}))
 
3. M_PS = [# of Proposals Awarded_PS]/[# of Proposals_WCO_PS]
 
I need to get the below data when i used the above measures but unfortunately i can't get that. 
Pravallika_L_0-1674578275373.png

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!!!!

 

Pravallika_L_0-1674635104415.png

 

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

Pravallika_L_0-1674638594291.png

Pravallika_L_1-1674638614446.png

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.

1.png

Sorry i am not getting what you are trying to convey. I have used the below measure for calculating the column total 

1. # of Proposals_Total_column =
DIVIDE (
    v_dyn_opportunity[# of Proposals Awarded_Sum_PS&USG],
    CALCULATE (v_dyn_opportunity[# of Proposals_WRC_Sum_PS&USG], ALLSELECTED(v_dyn_opportunity))
)
 
2. summary =
SUMX (
    SUMMARIZE ( v_dyn_opportunity,v_dyn_opportunity[Lines_of_Business], v_dyn_opportunity[Sector] ),
    v_dyn_opportunity[# of Proposals_Total_column]
)
 Pravallika_L_2-1674641357977.png

 

and then placed that measure in the above code which you have given but it shows me in this way. I know i am doing it in a wrong way. The values doesn't seem correct.
 
The below measure i have used to assign values for the field sector which i have used the values as columns in the matrix :
# of Proposals_PS&USG values =
if(HASONEVALUE(v_dyn_opportunity[Sector]),
SWITCH(SELECTEDVALUE(v_dyn_opportunity[Sector]),
"Private Sector", [# of Proposals_PS_divide],
"USG",[# of Proposals_USG_divide]
)
)
Pravallika_L_0-1674641225369.png

 

 I just need to show the row total and column total and the grand total based on these calculations
Pravallika_L_3-1674641486497.png

 

for total column -

Pravallika_L_4-1674641582182.png

for total row - 

Pravallika_L_5-1674641610874.png

and # proposals row - 

Pravallika_L_6-1674641641729.png

and the grand total 

Pravallika_L_7-1674641679964.png

 

@Pravallika_L 
Can we connect via teams or zoom?

Hi,

When can we connect please?

Now

Sure. But i don't have personal id in teams. Can you send me an invite? 

@Pravallika_L 

Waiting for you to join

Hi,

 

I have mailed you the data and the formulas.

Thankyou

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors