Summarize based on two columns

[Michaelbean21]

Hi there, I am trying to get an accurate count of DIMBRANCHID's based on BILLTODIMENTITYID.

For the case above my "test" column should come back as a count of 2 as there is two rows for the same BILLTODIMENTITYID, but there is two DIMBRANCHID's associated with the BILLTODIMENTITYID.

[tackytechtom · Accepted solution]

Hi @Michaelbean21 ,

I assume, you would like to create a calculated column which returns the DISTINCT COUNT of DIMBRANCHID (RandomAttribute1)  per BILLTODIMENTITYID (GroupingCol). Is this correct? If so, then this one down here should help you:

TableSummarizeCol = 
VAR _groupingCol = TableGroup[GroupingCol]
RETURN
CALCULATE ( DISTINCTCOUNT ( TableGroup[RandomAttribute1] ), ALL ( TableGroup ), TableGroup[GroupingCol] = _groupingCol)

If you are only interested in the number of row appearences per group (and independently of the other attribute), then this one should work:

TableSummarizeCol = 
VAR _groupingCol = TableGroup[GroupingCol]
RETURN
CALCULATE ( COUNTROWS ( TableGroup ), ALL ( TableGroup ), TableGroup[GroupingCol] = _groupingCol)

Let me know if this helps! If not, feel free to provide some sample data 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/