Sum and average in Power BI

[Sylwia_com]

Hi Community!

I was advised by a colleague that this forum could be proper place for finding help. I have following use case:

There is a dataset (Main_Table) containing following dimensions:

Region (F),

Market (F),

Branch (F),

Supervisor ID (F),

Company ID,

Plan ID,

Score Rank (F),

Major Industry (F),

Major Plan Category (F),

Company Generosity rating (F)

And measure:

Net Assets [$].

Net assets is provided on Company ID level, however, one company might have many Plan IDs. I would like to build a bar chart visual like below (actually a table where last value is displayed as bars, I know it is possible in PowerBI) where I can show one of selected categories Region/Market/Branch and sum of average assets on company ID level for selected category. I cannot make simple sum, because rows duplication due to Plan ID. I firstly need to calc average for each company and then sum these values.

As mentioned, user could make possibility to switch dimensions Region/Market/Branch for these measure I try to find.

This rank on below visual is build basing on calculated measure I am trying to figure out.

In addition, my calculation shall be affected by slicers, marked through (F) next to each column name above.

Could you please help?

Thanks in advance!

[V-yubandi-msft]

Hi @Sylwia_com ,

@DataNinja777 has provided a helpful response that addresses your question. Please review it and let us know if you need any more information or assistance. We are available to help if you require further support.

Thank you for your input, @DataNinja777 .

Best regards,
CST Team.

[DataNinja777]

Hi @Sylwia_com ,

The challenge you're facing is a common one in Power BI when dealing with data at different levels of granularity. To solve this, you'll first create a specific DAX measure to correctly calculate the sum of assets and then use a feature called Field Parameters to allow users to dynamically switch the viewing dimension.

To begin, you need to create a new measure that avoids the inflation caused by Plan ID duplication. In the Modeling tab of Power BI, select New Measure and enter the following DAX expression. This formula correctly calculates the total assets by first finding the unique asset value for each company and then summing those unique values.

Sum of Company Assets = 
SUMX(
    VALUES('Main_Table'[Company ID]),
    AVERAGE('Main_Table'[Net Assets])
)

This measure works by using the SUMX function to iterate through a temporary table of unique Company IDs, which is generated by the VALUES function. For each unique Company ID, the AVERAGE function calculates the single, correct Net Assets value (since the asset value is the same for all plans of one company, AVERAGE, MIN, or MAX would all work here). SUMX then adds up these correct, individual company asset values to get the true total.

Next, to enable users to switch the visual's category between Region, Market, and Branch, you should use Field Parameters. To set this up, go to the Modeling tab, click New parameter, and choose Fields. A new window will appear where you can name your parameter something like "View by". Then, from your Main_Table, drag the Region, Market, and Branch columns into the fields box. Ensure the "Add slicer to this page" option is enabled and click Create.

Finally, you can build your visual. Add a bar chart to your report page. Drag the new Field Parameter (e.g., "View by") onto the Y-axis and your Sum of Company Assets measure onto the X-axis. Power BI will have already added a slicer for your new parameter, which will now allow you to dynamically change the dimension displayed in the chart. All your other slicers will continue to filter the results as expected.

Best regards,