Forum Discussion
Need Help regarding DAX Formulas for creating Matrix for the mentioned Values
Hi tamerj1 amitchandak ALLUREAN
Need help in calculating the below formulas in powerBI :
Data :
| Opportunity Name | Type of Proposal | Business Line | Sector | Proposal Status | Win Probability | Status | Status Reason | Sponsor | Sponsor Type | Sponsor Category | Total Cost | Total Admin Revenue | Total Program Revenue | Surplus/Deficit |
| Kalya | New | Application/Selection | Private Sector | Not Awarded | High | Open | In Progress | NagAdvait | Educational Institution | NULL | 0.00 | 0.00 | 0.00 | NULL |
| Opportunity31 | New | Application/Selection | USG | Pending Response | Medium | Open | In Progress | QA Account4 | Academic Program | Partner | 0.00 | 0.00 | 0.00 | 33.00 |
| Lead99 | New | Capacity-building/Partnerships | Private Sector | Awarded | Medium | Open | In Progress | QA Account4 | Academic Program | Partner | 300050 | 150000.00 | 140000.00 | 478 |
| Mahesh | New | Capacity-building/Partnerships | USG | Awarded | Medium | Open | In Progress | MAHLE Engine Components USA, Inc | Company | NULL | 0.00 | 0.00 | 0.00 | NULL |
| @TEST_Venkat_PBI | New | Children of Employee Scholarships | Private Sector | Pending Response | Medium | Won | Won | TEST Account | NULL | NULL | 5110050 | 100000.00 | 5000000.00 | 289 |
| @Venkat_ROI_Open | New | Children of Employee Scholarships | Private Sector | Pending Response | High | Open | In Progress | @Venkat & Co. | Educational Institution | Public | 610050 | 100000.00 | 500000.00 | 680 |
| @Venkat_ROI_REPORT_Out Sold | New | Children of Employee Scholarships | USG | Cancelled | Low | Lost | Out-Sold | @Venkat & Co. | Educational Institution | Public | 160050 | 100000.00 | 50000.00 | 600 |
| @Venkat_ROI2_Open | New | Children of Employee Scholarships | Private Sector | Awarded | Medium | Open | In Progress | Venkat | Government | Research Center/Institute | 140050 | 30000.00 | 100000.00 | 458 |
| AM_Lead | Renewal Rebid | Children of Employee Scholarships | Private Sector | Cancelled | High | Open | In Progress | Sala 1 – Centro Internazionale d’Arte Contemporanea | Non-Government Organization | NULL | 135050 | 100000.00 | 25000.00 | NULL |
| Opportunity with Slate | New | Children of Employee Scholarships | USG | Cancelled | High | Open | On Hold | JVN Test Institution | Educational Institution | College, Higher Ed Institution | 10050 | 0.00 | 0.00 | 77.90 |
| @Venkat_PBI_LOW | New | Diaspora Engagement | Private Sector | Awarded | Low | Won | Won | @Venkat & Co. | Educational Institution | Public | 310050 | 100000.00 | 200000.00 | 5000.00 |
| VENKAT_ROI_REPORT_Cancelled | New | Diaspora Engagement | Private Sector | Cancelled | High | Lost | Canceled | @Venkat & Co. | Educational Institution | Public | 2520050 | 10000.00 | 2500000.00 | 4400 |
| @Venkat_Test_Opty BPF | New | Education in Emergencies | USG | Awarded | Medium | Won | Won | @Venkat & Co. | Educational Institution | Public | 360050 | 100000.00 | 250000.00 | 567 |
| @Venkat_Test Lead_19Dec22 | Renewal Rebid | Placement | USG | Awarded | High | Open | In Progress | Venkat | Government | Research Center/Institute | 14731705 | 4806666.00 | 9914989.00 | 55.66 |
| QA Lead2 | New | Placement | USG | Awarded | Medium | Open | In Progress | QA Account4 | Academic Program | Partner | 0.00 | 0.00 | 0.00 | NULL |
| Ryan Allen New Opp 14 | New | Placement | USG | Pending Response | High | Open | In Progress | JVN Test Institution | Educational Institution | College, Higher Ed Institution | 0.00 | 0.00 | 0.00 | NULL |
| Leena | New | Study Tours | Private Sector | Pending Response | Medium | Open | In Progress | hamilton | NULL | NULL | 0.00 | 0.00 | 0.00 | NULL |
| Opportunity_AM1 | New | Training/Workforce Development | USG | Not Awarded | Medium | Open | In Progress | Adobe AM Participant Inst | Educational Institution | NULL | 0.00 | 0.00 | 0.00 | 4444.00 |
| Susan | Renewal Rebid | Training/Workforce Development | Private Sector | Pending Response | High | Open | In Progress | upEnd Movement | Company | NULL | 10050 | 0.00 | 0.00 | NULL |
| Test Opty | New | Travel and Learning Fund | USG | NULL | Medium | Open | In Progress | - Center for Spatial Technologies and Remote Sensing | Non-Government Organization | NULL | 0.00 | 0.00 | 0.00 | NULL |
| Test for email | Renewal Rebid | Visa Sponsorship | Private Sector | NULL | High | Open | In Progress | - Center for Spatial Technologies and Remote Sensing | Non-Government Organization | NULL | 10050 | 0.00 | 0.00 | NULL |
Formulas :
Output
Formula 2:
output : will be like above table in % but instead of count i need to sum and show them in %
Formula which I used till now with the help of tamerj1
1. Trial_# of Proposals Awarded =
CALCULATE (
COUNT ( v_dyn_opportunity[Lines_of_Business] ),
FILTER (
v_dyn_opportunity,
v_dyn_opportunity[Proposal_Status] = "Awarded"
)
)
2. Trial_# of Proposals_WCO =
CALCULATE (
COUNT ( v_dyn_opportunity[Lines_of_Business] ),
FILTER (
v_dyn_opportunity,
v_dyn_opportunity[Proposal_Status] IN { "Awarded", "Cancelled", "Not Awarded" }
)
)
3. Trial_# of Proposals_divide =
VAR ProposalsWCO =
IF (
ISINSCOPE ( v_dyn_opportunity[Sector] ),
CALCULATE (
[Trial_# of Proposals_WCO],
ALLSELECTED ( v_dyn_opportunity[Lines_of_Business] ),
ALL ( Lines_of_Business )
),
[Trial_# of Proposals_WCO]
)
RETURN
DIVIDE (
[Trial_# of Proposals Awarded],
ProposalsWCO
)
4. Trial_Final Measure =
VAR CurrentBusinessLine = SELECTEDVALUE ( Lines_of_Business[Lines_of_Business] )
VAR NormalValue = [Trial_# of Proposals_divide]
VAR NumOfProposals = CALCULATE ( [Trial_# of Proposals Awarded], ALL ( Lines_of_Business ) )
VAR Result =
IF (
CurrentBusinessLine = "# Proposals",
FORMAT ( NumOfProposals, "#" ),
NormalValue
)
RETURN Result
and the output i have got by using the above formulas is not exactly the right output shown below :
But i also couldn't perform the color coding for individual Columns like for Private Sector one set of color codes and for USG another set of color codes. That too if there are blanks then they must have white background. And also the entire #Proposals row background must be white & should be in bold with font color blue. Exactly the output should be like the one i showed at the start for the first formula.
The second formula should not have #Proposals in it.
Can anyone please help me with this. I also have another tables but first i will go with this
Thanks in Advance
2 Replies
Hi All,
Got the output like i displayed at the start. I just removed the line ALL ( Lines_of_Business ) from the below formula. But I was not getting the 0% in total and list of business lines which atleast has some Proposals like not awarded, Canceled,... they must also be in the list with a blank value. tamerj1 can you please help me out.
3. Trial_# of Proposals_divide =
VAR ProposalsWCO =IF (ISINSCOPE ( v_dyn_opportunity[Sector] ),CALCULATE ([Trial_# of Proposals_WCO],ALLSELECTED ( v_dyn_opportunity[Lines_of_Business] )),[Trial_# of Proposals_WCO])RETURNDIVIDE ([Trial_# of Proposals Awarded],ProposalsWCO)