Avg sum of a dynamic table using slicers

[AdamGarcia_SCTA]

Hello,

I am trying to create a Card in Power BI that shows the average sum or average total of a dynamic table that changes by a slicer. The table slicer has four variables including year, sector, sector type, and jurisdiction. The units are in CO2e. I believe I must create a new measure but could use some guidance with this many variables.

Slicer Variables:

Sample Table Calculated in Power BI:

Example Excerpt of Source Table:

 

The Card would show the average of the yearly totals (highlighted) for all or some of the jurisdictions (depending on the slicer selection) at the bottom of the Sample Table Calculated in Power BI, which could also change by any of the four slicer variables. In this case, the card would show 3,555,840 as the average of the five yearly totals of 3,943,999, 3,867,079, 3,448,035, 3,478,802, and 3,041,286.

Any guidance that can offer would be appreciated.

[Anonymous]

HI @AdamGarcia_SCTA,

I think you can create a table visual with these filter fields.

Then you can write a measure formula with switch function to check the current value to redirect to different expression parameterized these formula with current value as condition to get the dynamic results.

BTW, can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[AdamGarcia_SCTA]

Here are a few screenshots showing how the card visualization would take the average of a dynamic table. Here is the Power BI file: https://drive.google.com/file/d/13Pr0zEFEJLBsWBBb5LMuuKsVhBsMKlw1/view?usp=drive_link

[lbendlin · Accepted solution]

[AdamGarcia_SCTA]

Perfect lbendlin, thank you for the help!