Forum Discussion
Create calculated measures for filtered table calculations
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
2 Replies
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
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!
