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

21777320222
21777320231
21777320241
23432520231
23432520242

 

Goal

I want to create a dynamic Power BI visual where:

  • X-axis: Sum of Count
  • Y-axis: Distinct count of Reporter

The visual should dynamically adjust when filtering Year.

 

Expected Results

If no filter is applied (all years selected), the visual should show:

 

  • 1 reporter (217773) with 4 counts
  • 1 reporter (234325) with 3 counts

    like this:

     

If 2024 is selected:

  • 1 reporter (217773)  with 1 count
  • 1 reporter (234325) with 2 counts

If 2023 is selected:

  • 1 reporter (217773) with 1 count
  • 1 reporter (234325) with 1 count

 

 

If 2023 & 2024 are selected:

  • 1 reporter (217773) with 2 counts
  • 1 reporter (234325) with 3 counts


Problem

A major issue I identified is that measures cannot be used as an X-axis in Power BI.

How can I solve this issue and make the X-axis dynamic while allowing the filtering of Year?

Any help would be greatly appreciated! 🚀


Cheers
qwertzuiop

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.

     

2 Replies

  • 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.