Forum Discussion
How do I aggregate a column in a table
I am trying to create a column to aggregate the data below by market and fiscal month. In Excel it is quite easy using a COUNTIFS function i.e. MonthlyLeads = COUNTIFS(B:B,B2,J:J,J2) But I can't figure out the equivalent DAX to do this.
| ID | Market | apptdate | FiscalDayOfMonth | FiscalDayOfYear | FiscalWeekOfYear | fiscalMonthOfYear | fiscalQuarter | fiscalYear | FiscalMonthKey | MonthlyLeads |
| 118554 | ORL | Thursday, January 28, 2021 | 26 | 26 | 4 | 1 | 1 | 2021 | 1/1/2021 | 330 |
| 152472 | ORL | Monday, January 3, 2022 | 2 | 2 | 1 | 1 | 1 | 2022 | 1/1/2022 | 343 |
| 123318 | JAX | Wednesday, March 10, 2021 | 11 | 67 | 10 | 3 | 1 | 2021 | 3/1/2021 | 234 |
| 183521 | TAMPA | Monday, August 22, 2022 | 23 | 233 | 34 | 8 | 3 | 2022 | 8/1/2022 | 190 |
| 186942 | SAR | Wednesday, October 5, 2022 | 4 | 277 | 40 | 10 | 4 | 2022 | 10/1/2022 | 274 |
| 182727 | SAR | Friday, August 12, 2022 | 13 | 223 | 32 | 8 | 3 | 2022 | 8/1/2022 | 111 |
| 186301 | SAR | Saturday, September 24, 2022 | 28 | 266 | 38 | 9 | 3 | 2022 | 9/1/2022 | 107 |
| 132147 | JAX | Tuesday, June 1, 2021 | 3 | 150 | 22 | 6 | 2 | 2021 | 6/1/2021 | 162 |
To give a little context here I have the financial account data by Month and Market and we want to do some calculations like marketing cost per lead etc. I can easily do a lookup of the marketing spend, but I have to average it out to use in my measure then divide by the count of leads. So when I display that in a table I get good data for the individaul markets and months, but the totals are way too low since it is taking the average total divided by all leads.
I have also tried to just join the tables on Market and make a single measure but since I am using measures from two tables and slicing by market and month I am getting results that are way off.
Since I have the aggregate marketing cost in my leads table, via lookup, I want to also have an aggregate of leads. That way they can both be averaged in my measure and elminate the discrepancy that only exists in the total.
If there is an easier option I am open to that but like I said in Excel this would be easy. Any help here would be appreciated.
I managed to figure it out. I needed to add a column that combines both criteria and then calculates it using a COUNTA and all ALLEXCEPT
MarketMonth = ils_IssuedLeads[Market] & " - " & ils_IssuedLeads[FiscalMonthKey]2 test = CALCULATE ( COUNTA ( ils_IssuedLeads[MarketMonth] ), ALLEXCEPT(ils_IssuedLeads, ils_IssuedLeads[MarketMonth] ) )
1 Reply
I managed to figure it out. I needed to add a column that combines both criteria and then calculates it using a COUNTA and all ALLEXCEPT
MarketMonth = ils_IssuedLeads[Market] & " - " & ils_IssuedLeads[FiscalMonthKey]2 test = CALCULATE ( COUNTA ( ils_IssuedLeads[MarketMonth] ), ALLEXCEPT(ils_IssuedLeads, ils_IssuedLeads[MarketMonth] ) )