Calculated column that aggregates over N number of rows

[pjohnson]

We have a dataset were the row granularity is not fixed. For each widget in the dataset, we can have 1 to N rows of data that describes it. We often create calculated fields in Tableau that aggregate a field over all rows of each widget. Here is a simplistic example:

Line # is the widget identifier

Series is the product series for that row of data

Line-Series is the aggregated value

Line #	Series	Line-Series
1	A	A
1	A	A
2	A	multi
2	A	multi
2	B	multi
3	C	C

In Tableau, this is a simple Fixed Level Of Detail calculation:

fixed [Line #]: If COUNTD(Series) > 1 then "multi" else max(Series) END 

We then take this calculated field and use it like a normal dimension in all aspects of reporting (sum $ by it, use it as a report filtert, etc) in Tableau.

How do I do this in Power BI?

TIA!

[Anonymous · Accepted solution]

Hi,

I suppose the 'Line-series' field is what you are looking for.

I used the below DAX to generate the calculated column.

Line-Series = 
VAR v_SeriesCnt = CALCULATE
( 
    DISTINCTCOUNT( SeriesData[Series] ), 
    ALLEXCEPT( SeriesData, SeriesData[Line] ) 
 )
 RETURN
 IF( v_SeriesCnt > 1, "Multi", CALCULATE( MAX( SeriesData[Series] ), ALLSELECTED( SeriesData[Series] ) ))

Thanks.