topic Re: Create a Matrix with % for individual types based on the count of categories in DAX Commands and Tips

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

Anonymous — Wed, 25 Jan 2023 15:13:36 GMT

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

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

tamerj1 — Tue, 24 Jan 2023 15:54:41 GMT

Hi Anonymous

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

)

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

Anonymous — Tue, 24 Jan 2023 16:42:37 GMT

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.

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

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

tamerj1 — Tue, 24 Jan 2023 17:13:37 GMT

Anonymous
Please try

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

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

Anonymous — Wed, 25 Jan 2023 09:04:44 GMT

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

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

tamerj1 — Wed, 25 Jan 2023 09:12:52 GMT

Anonymous
The total number of proposals is just the denominator

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

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

Anonymous — Wed, 25 Jan 2023 09:25:31 GMT

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?

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

tamerj1 — Wed, 25 Jan 2023 09:45:08 GMT

Anonymous
Please try

= SUMX ( SUMMARIZE ( v_dyn_opportunity[Lines_of_Business], v_dyn_opportunity[Sector] ), [Your Measure] )

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

Anonymous — Wed, 25 Jan 2023 09:48:10 GMT

what Measure should I add in the place of [Your Measure]?

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

tamerj1 — Wed, 25 Jan 2023 09:50:11 GMT

Anonymous
The measure that you are using in this matrix. Then place the new measure instead.

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

Anonymous — Wed, 25 Jan 2023 10:15:53 GMT

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]

)

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]

)

I just need to show the row total and column total and the grand total based on these calculations

for total column -

for total row -

and # proposals row -

and the grand total

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

tamerj1 — Wed, 25 Jan 2023 10:25:59 GMT

Anonymous
Can we connect via teams or zoom?

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

Anonymous — Wed, 25 Jan 2023 12:22:54 GMT

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

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

Anonymous — Wed, 25 Jan 2023 11:57:30 GMT

Hi,

When can we connect please?

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

tamerj1 — Wed, 25 Jan 2023 12:05:16 GMT

Now

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

tamerj1 — Wed, 25 Jan 2023 12:35:41 GMT

Anonymous

Waiting for you to join

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

Anonymous — Wed, 25 Jan 2023 15:00:24 GMT

Hi,

I have mailed you the data and the formulas.

Thankyou

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

Anonymous — Wed, 01 Feb 2023 14:14:21 GMT

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?

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

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