Reference a Column Resulting from a Summarize Function, Within the Same Table Function??

[mm5308]

Tried searching for this topic for a while but honestly I'm not even sure how to word what I'm trying to accomplish, so I'll explain it.

My main table contains a couple dozen columns and I'm trying to create a summary table to show the relationship between two of them. The first column is BatchID. It's almost a unique key field except that some of the BatchIDs are repeated. That happens when a transaction is recorded in one month then reversed the following month. The second column is of course the MonthNum.

I created a summary table as follows:

SumTable = 

SUMMARIZE('Tbl',

'Tbl'[BatchID],

'Tbl'[MonthNum])

This produces, as I want, a table of about 12,000 rows.

Here's where the tricky part comes. What I really want to do with this table is add a calculated field that references one of the columns I just created. Technically, if I were to create a table of distinct values of the BatchID field from the main table { = DISTINCT('Tbl'[BatchID]) }, the total number of rows would be 10,000. Again, that's because 2,000 of those BatchIDs are repeated in another month.

So what I want to do is: perform what would essentially be a =COUNTIF() function in Excel on the BatchID column from the SumTable I just created. The problem is that I don't know how to reference that column from the SUMMARIZE function because I'm still within the table creation process at that point.

To illustrate, this is what I tried:

SumTable =

ADDCOLUMNS(

SUMMARIZE('Tbl',

'Tbl'[BatchID],

'Tbl'[MonthNum]),

"Count",COUNT('Tbl'[BatchID]))

I'm sure you can already spot the problem: my COUNT function at the end points to the main table so every row of the SumTable displays the same huge value, which is the count of the BatchIDs from the main table.

Is it possible to change that "Count" function to reference not the BatchID field from the main table, but instead reference the BatchID field from the nested SUMMARIZE function?

I'm a relative beginner so I'm not sure how to do this, and again, I've tried and researched unsuccessfully.

[davehus · Accepted solution]

Hi @mm5308 ,

Try this column measure to see if it gets you the desired outcome.

Count Batches = CALCULATE(COUNTA('Table'[BatchID]),ALLEXCEPT('Table','Table'[BatchID]))

[mm5308]

Yes, that worked perfectly! Thank you @davehus! Now I need to learn more about ALLEXCEPT...

[davehus]

No problem, every day is a school day with PowerBI. 🙂

[davehus]

Hi @mm5308 ,

I'm not sure if this will help as you are saying that there is a countif so you might have an additional clause to add. You could create a measure like below.

CALCULATE(COUNTROWS(

SUMMARIZE('Tbl',

'Tbl'[BatchID],

'Tbl'[MonthNum])))

Let me know if this is on the right track?

[mm5308]

Hi @davehus ,

I probably should've clarified that I'm looking to create a table - not a measure - that displays all ID and MonthNum values, plus that "count" column I want to add. As far as the "COUNTIF" reference, I only brought it up to illustrate what I would do in Excel. In that environment I'm pretty savvy. Translating that knowledge to the "language" of Power BI, however, is where I struggle. Hope this explanation clarifies, and thanks for your input!

[davehus]

Hi @mm5308 ,

Just had a flashback to something I had to do before. Create your summarize table as outlined. Go to the Data Tab in the designer and then add column with your desired measure referencing the new table. You will need to wrap the measure in calculate, as if you use say a countrows() it will return the total row number in each row

[davehus]

Hi @mm5308 , No problem, have you an excel screengrab of your desired output with dummy data and I'll see what I can do for you. 🙂

[mm5308]

Hi @davehus,

Thanks for suggestion to add a column and then a formula. I actually never thought of simply adding a column! Seems so obvious now, although I guess I was focused on making that step part of the table creation formula.

Unfortunately, the formula did not work. Every value was a 1 because I think it was simply counting each row as opposed to adding the qualifier to count "values like the one on the current row". Perhaps the formula needs a FILTER modifier?

Additionally, please see the screenshot below for an example of what I'm looking for (...in Excel since I can't figure out how to actually do it in PBI yet).