Create calculated measures for filtered table calculations

[lkay]

Hello Pbi Community, Any help on the following use case would be highly appreciated. Still new to Power Bi.

Is it possible to create measures for the following calculations in powerbi? What would be the best way for this?

Requirements:

• COUNT of Households created in Current Month based on field si_startdate
• COUNT of Warm Opportunities estimated to be closed in Current Month based on:
  • Warmth: OpportunityRatingCode = 1,2
  • Close Date: Estimateclosedate = Current Month
• COUNT of Target NEW Households for Current Month
  • Custom manual target table

• SUM of Total Market Value of Households created in Current Month based on field si_startdate
• SUM of projected investment on Warm Opportunities to be closed in Current Month:
  • SUM of _budgetamount
  • Warmth: OpportunityRatingCode = 1,2
  • Close Date: Estimateclosedate = Current Month
• Total of Target new Assets for Current Month
  • Custom manual target table

Custom manual target table:

Month	Target Cnt	Target Total Assets
1	10	1,000,000
2	12	2,000,000

Goal:

Create measures to be able to plot cards, a stacked column chart or a pie graph to show the 3 metrics.

I was able to do this by creating an individual visual card and putting the criteria on each card, but is there a way to do this via a measure?

Thank you

[lkay]

hello @amitchandak 

Seems like we cannot upload pbix files here.

Another way to ask the question - what would be the formula for a DAX measure that calculates: 

• COUNT of Households and Total Maket Value $ in Current Month based on field start date 
• measure answers would show = 2 HH; $1,000,000

Household table:

HH Name	Start Date	Total Market Value
HH1	2022-08-06	$500,000
HH2	2022-09-05	$500,000
HH3	2022-09-10	$500,000

COUNT of Warm (1,2 only) Opportunities estimated to be closed in Current Month based on and associated Projected Investment:

Measure answers would show = 1 Opportunity; $200,000

Opportunity Table

Opp Name	Close Date	Warmth	Projected Investment
Opp1	2022-09-10	1	$200,000
Opp2	2022-09-20	3	$400,000
Opp3	2022-11-10	1	$150,000

thank you!

[amitchandak]

@lkay ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Check this one- userelationship , date table, and how to deal with more than one date

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...