Represent Dynamic Table Visual based on Slicer Selection

[prabhatnath]

Hi Friends,

I have a requirement where I need to represent data in the below format so what is the best visual I can use and how can I achieve the same?

Columns 1 and 3 are Fixed headers while the Value are in Columns 2 and 4 which are displayed from the Measures. The Measure values depend on the Slicer selections. The percent can be represented as a progress bar if feasible.

Possibly we can create a dynamic table visual as suggested by @V-lianl-msft at another thread https://community.fabric.microsoft.com/t5/Desktop/Create-dynamic-table-based-on-the-slicer-selection...  but I am not sure how we can do that? Any steps for this dynamic Table Visual will be a great help too.

Feature	<Value from Measure>	Description	<Value from Measure>
Start Date	<Value from Measure>	End Date	<Value from Measure>
Capacity	<Value from Measure>	Work Assigned	<Value from Measure> (%)
Completed	<Value from Measure> (%)	Pending Work	<Value from Measure> (%)

Thanks,

Prabhat

[Anonymous]

Hi @prabhatnath ,

If I understand correctly, you want to display the second and forth field dynamically base on the slicer selection. And the forth field will display with progress format. Am I right? Could you please provide some source data in the excel files (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[prabhatnath]

Thanks  v-yiruan-msft for responding.

Here is the sample data that can be used for this purpose.

In the data "Program Name" and "Sprint Name" are used in the slicer so that report data are based on those selections.

I need help adding a few summaries at the top of the report as below. Columns 1 and 3 are Fixed row headers for all selections and columns 2 and 4 values should be shown dynamically based on the Slicer selection.

I already have the Measures for Columns 2 and 4.

Selected Sprint	Sprint S103	Original Estimate	289
User Stories	39	Completed Work	69
Story Points	117	Remaining	210

Selected Sprint = Selected value from the Sprint Name Slicer.

User Stories = Count of Records of "Work Item Type" = User Stories and "Is Current"  = True (for the selected Program and Sprint)

Story Points  = SUM of "Story Points" where "Is Current" = True (for the selected Program and Sprint) 

Original Estimate = SUM of "Original Estimate" where "Is Current"  = True (for the selected Program and Sprint) 

Completed Work = SUM of "Completed Work" where "Is Current"  = True (for the selected Program and Sprint) 

Remaining = SUM of "Remaining Work" where "Is Current"  = True (for the selected Program and Sprint) 

Thanks,

Prabhat

[Anonymous]

Hi @prabhatnath ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Count of User Stories = 
CALCULATE (
    COUNT ( 'data'[Sprint Name] ),
    FILTER (
        'data',
        'data'[Work Item Type] = "User Story"
            && 'data'[Is Current] = TRUE ()
    )
)

Sum of Story Points = 
CALCULATE (
    SUM ( 'data'[Story Points] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)

Sum of Original Estimate = 
CALCULATE (
    COUNT ( 'data'[Original Estimate] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)

Sum of Completed Work = 
CALCULATE (
    SUM ( 'data'[Completed Work] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)

Sum of Remaining Work = 
CALCULATE (
    SUM ( 'data'[Remaining Work] ),
    FILTER ( 'data', 'data'[Is Current] = TRUE () )
)

Best Regards

[prabhatnath]

Thank you for your help on this.

Actually, I am able to get a table as normal with columns as headers and rows as values, but I was looking to represent the table as below:

Where Columns 1 and 3 are Headers/Fixed string and Columns 2 and 4 are based on Slicer selection..

Selected Sprint	Sprint S103	Original Estimate	289
User Stories	39	Completed Work	69
Story Points	117	Remaining	210

Sorry if my earlier question was not clear.

Thanks,

Prabhat

[Anonymous · Accepted solution]

Hi @prabhatnath ,

I updated my sample pbix file(see the attachment), please check if that is what you want.

1. Create a dimension table as below

2. Create a measure as below

Measure = 
SWITCH (
    SELECTEDVALUE ( 'Table'[Type] ),
    "User Stories", [Count of User Stories],
    "Story Points", [Sum of Story Points],
    "Completed Work", [Sum of Completed Work],
    "Remaining Work", [Sum of Remaining Work]
)

Best Regards