Forum Discussion

qwertzuiop's avatar
qwertzuiop
Advocate III
1 year ago
Solved

Dynamic Aggregation and Visualization of Reporters by row_sum

Hello Dear Power BI Community, I'm writing this post because, even with ChatGPT, I couldn't solve my problem.   Dataset Example Let's assume I have the following table: Reporter Year Count 2...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi qwertzuiop 

     

    You will need to materialize the count using a table containing a column of all possible counts.

    CountsTable = 
SELECTCOLUMNS ( GENERATESERIES ( 1, 10, 1 ), "Count", [Value] )

     

    Count Materialized = 
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'Table'[Reporter], "@value", [Sum of Count] ),
        [@value] IN VALUES ( CountsTable[Count] )
    )
)

    Please see the attached sample pbix.

     

danextian's avatar
danextian
Super User
1 year ago

Hi qwertzuiop 

 

You will need to materialize the count using a table containing a column of all possible counts.

CountsTable = 
SELECTCOLUMNS ( GENERATESERIES ( 1, 10, 1 ), "Count", [Value] )

 

Count Materialized = 
COUNTROWS (
    FILTER (
        SUMMARIZECOLUMNS ( 'Table'[Reporter], "@value", [Sum of Count] ),
        [@value] IN VALUES ( CountsTable[Count] )
    )
)

Please see the attached sample pbix.