The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Using the sample data below, I have a requirement to show the Average Close Rate in the Total Line of a table visual rather than the current result which is the % Closed of Opportunities. In this example, the end users would want to see 57.2% in the Total line of the Close Rate because (58.5+50.3+48.5,+72.3+77.1+36.9+71.0+43.4 = 458)/(8) = 57.25%
State | Opportunities | Closed | Close Rate |
OH | 1,163 | 680 | 58.5% |
OK | 1,021 | 514 | 50.3% |
OR | 1,474 | 715 | 48.5% |
PA | 1,001 | 724 | 72.3% |
RI | 1,096 | 845 | 77.1% |
SC | 1,431 | 528 | 36.9% |
SD | 1,030 | 731 | 71.0% |
TN | 1,236 | 536 | 43.4% |
Total | 9,452 | 5,273 | 55.8% |
Hi,
Try this measure
Measure = averagex(values(Data[State]),[close Rate])
Hope this helps.
Hello! I used the below and got 57.25%
Proud to be a Super User! | |
I think maybe I wasn't clear in my set up. The sample I posted is actually the table visual in my report. In the table visual I've brought in columns for opportunities, closed, and closed rate.
Opportunities = COUNT(OPPORTUNITY_ID)
CLOSED = CALCULATE ([OPPORTUNITIES],'SALES_TABLE' [SALES_STATUS] = "CLOSED")
CLOSED RATE = DIVIDE(Closed,Opportunities)
It's very possible I'm missing something, but I think the solution you provided only works if I already have a table of summarized data?
AVERAGEX is an iterator function that first sums each row then averages the sums: https://learn.microsoft.com/en-us/dax/averagex-function-dax#example
Proud to be a Super User! | |