topic Re: Avg sum of a dynamic table using slicers in DAX Commands and Tips

Avg sum of a dynamic table using slicers

AdamGarcia_SCTA — Thu, 30 Nov 2023 20:43:45 GMT

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.

Re: Avg sum of a dynamic table using slicers

lbendlin — Sun, 03 Dec 2023 02:45:26 GMT

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-Forum/ba-p/963216

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/1447523

Re: Avg sum of a dynamic table using slicers

Anonymous — Wed, 06 Dec 2023 03:19:41 GMT

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

Re: Avg sum of a dynamic table using slicers

AdamGarcia_SCTA — Tue, 12 Dec 2023 22:52:54 GMT

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

Re: Avg sum of a dynamic table using slicers

lbendlin — Wed, 13 Dec 2023 00:58:54 GMT

Re: Avg sum of a dynamic table using slicers

AdamGarcia_SCTA — Thu, 14 Dec 2023 17:28:57 GMT

Perfect lbendlin, thank you for the help!