Column averages table with ascending values and dynamic date slicer

[Marty_H]

Hi everyone,

I have a table that contains data with daily values like this:

Now I want to create a table with the averages in ascending order that adjusts itself when moving the date slicer in my dashboard.
I know that the treemap visual works in a similair way in my dashboard, but my customer wants a table view.
On the left of my treemap visual you can see 3 failed attempts:



It must be dynamic, so when I slice for a different period in time, my table needs to adjust.
The averages need to be dynamically sorted from highest value to lowest (just like the treemap does).
The first attempt is with a table visual, but this doesn't allow me to get my columns as rows.
The second attempt is the matrix visual, but here I can't have my values sorted in ascending order.
The third attempt is a calculated column but this doesn't adjust with the slicer (see more info below)

This sounded simple but it seems like this is not that easy on power bi... 🤔

The calculated table I made has been made like this:

But tables made like this are not affected by slicers. 😔

Is there someone who can help me with this seemingly simple problem?

Here is my pbix file: Column_averages_table_ascending_values_with_date_slicer.pbix

And here are some data files:

• Heatmap Data 1
• Heatmap Data2

Thanks in advance,
Marty

[V-lianl-msft]

Hi @Marty_H ,

Try using "Unpivot" in the query editor, and then create a measure to calculate the average.

https://radacad.com/pivot-and-unpivot-with-power-bi 

The calculated table and calculated column are always static, and the value cannot be changed dynamically according to the slicer. Only measure will change dynamically based on the current row context.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The link requires a login.

[Marty_H]

Thanks for letting me know lbendlin,
I didn't realize it required a password.

Now everyone should be able to download it

[lbendlin]

Now I see what your issue is.  You need to do some serious unpivoting with your raw data to make it useful.

Can you post a couple of the files in the PBI Test Folder 2\HeatMap_Data\ folder?

[Marty_H]

I added some data files to my original message for you.

If I unpivot my data, the dates (rows) would become the columns.
If I want to add them to a table, are you sure that extra data (more data with every day added) will be added with a refresh?
Because I believe new columns don't get added to table visuals automatically after a refresh or am I wrong here?

I'm gonna try it out.
In general what the treemap visual does in my pbix file is what I would like to see in a table visual with the names in the columns of the original data and next to it the exact percentages.
Now I need to hover over all the squares in the treemap visual to see the percentages.

This doesn't provide me with an accurate quick overview of these exact percentages.

[lbendlin]

Here is the query to combine all your files and then unpivot them to make them usable.

let
    Bron = Folder.Files("C:\Users\xxx\Downloads"),
    #"Filtered Rows" = Table.SelectRows(Bron, each Text.Contains([Name], "HeatMapData") ),
    #"Aangepaste functie aanroepen1" = Table.AddColumn(#"Filtered Rows", "Bestand transformeren", each #"Bestand transformeren"([Content])),
    #"Namen van kolommen gewijzigd1" = Table.RenameColumns(#"Aangepaste functie aanroepen1", {"Name", "Source.Name"}),
    #"Andere kolommen verwijderd1" = Table.SelectColumns(#"Namen van kolommen gewijzigd1", {"Source.Name", "Bestand transformeren"}),
    #"Expanded Bestand transformeren" = Table.ExpandTableColumn(#"Andere kolommen verwijderd1", "Bestand transformeren", Table.ColumnNames(#"Andere kolommen verwijderd1"{0}[Bestand transformeren])),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Bestand transformeren", {"Source.Name", "Day"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type number}})
in
    #"Changed Type"

see attached