Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am working on sales vs. quota dashboard for each salesperson. I have 4 tables in my data model right now:
1. Hierarchy (manager, employee name)
2. Sales (fiscal period, employee name, sales total)
3. Quotas (Fiscal Period, employee name, quotas)
4. dimFiscalPeriod (fiscal period lookup table)
The sales and quota table are both linked to the dimfiscalperiod tableand the hierarchy table.
I currently have a table visual which shows employee name, sales, quota and a measure I created of % of quota hit. I also have a slicer for fiscal period that filters the table data based on which fiscal period is selected.
I want to have a card on the dashboard that shows the number of employees above quota, or in other words number of employees whose % of quota hit is > 100%. I want this card to change depending on which fiscal period is selected in the slicer. I can't seem to get it to work. I've uploaded a dummy data model here. Any help you can provide would be very helpful. I basically just want to show a summary of what the table visual is showing in the card, but it's not working.
DROPBOX LINK: https://www.dropbox.com/s/bthj393u0m8rtry/Dummy%20Prez.pbix?dl=0
Solved! Go to Solution.
Bless you for providing a PBIX file! 👍
Does this return what you want? It filters the Hierarchy table then just counts the names. Theoritcially it could fail if the name was blank, but I prefer this vs a COUNTROWS as that requires a CALCULATE, and that should not be necessary for this.
Above Quota =
COUNTX(
FILTER(
'Hierarchy',
[% of Quota Hit] > 1
),
'Hierarchy'[Name]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingBless you for providing a PBIX file! 👍
Does this return what you want? It filters the Hierarchy table then just counts the names. Theoritcially it could fail if the name was blank, but I prefer this vs a COUNTROWS as that requires a CALCULATE, and that should not be necessary for this.
Above Quota =
COUNTX(
FILTER(
'Hierarchy',
[% of Quota Hit] > 1
),
'Hierarchy'[Name]
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis is perfect. Thank you!
Great @Anonymous ! Glad your project is moving forward.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |