Dynamic Total Calculation Issue- Need Help on achieving the correct totals

[Deepikapbi_9]

I have a Power BI report with specific country information containing sales details for different manufacturers and segments.
The report utilizes two slicers: one for selecting manufacturers and another for selecting segments.
The aim is to calculate the percentage share of each manufacturer's sales within the selected segment(s).
However, not all manufacturers have sales data for all segments, leading to inaccuracies in the percentage calculation.

When a Segment is selected, the total sales should adjust for all Manufacturers, and when a Manufacturer is selected, it should display the total sales across all selected Segments, even if there are no sales recorded for those Segments under the chosen Manufacturer.

In the above image if Manufacturer A is selected the desired total sales should come out as 23000.

Any guidance or assistance on how to achieve the desired behavior would be greatly appreciated.

[Deepikapbi_9]

Hi @v-heq-msft 

I've put your suggestions into action and experimented with using both "SUM BY SEGMENT" and "Total Sales" as denominators for calculating the percentage. However, the outcomes haven't quite matched our expectations.

In the example I showed earlier, I only used the manufacturer and segment info. But in reality, we also have a country column.

When I use "total sales" to calculate the percentage, it's fine. But when I pick a manufacturer, the numbers go wonky. Both "total sales" and "sum by segment" act like the total sales are 22990 for manufacturer A, which isn't right. So, the percentage ends up being wrong too.

[v-heq-msft]

Hi @Deepikapbi_9 ，
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create measures

SUM = SUM('Table'[Sales])

SUM BY SEGMENT = 
CALCULATE(
    [Total Sales],
    REMOVEFILTERS('Table'[MANUFACTURER]),
    VALUES('Table'[SEGMENT])
)

Total Sales = 
CALCULATE(
    SUM('Table'[Sales]),
    ALL('Table'[MANUFACTURER])
)

Percentage = [SUM]/[SUM BY SEGMENT]

These will calculate the percentage of the same SEGMENT that each MANUFACTURER accounts for, and if you want to display the total even after selecting the MANUFACTURER, you can do so by simply selecting the option in the MANUFACTURER
Final output

Best regards,

Albert He

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

[Deepikapbi_9]

PS : Measure for Total Sales = CALCULATE(SUM('Table'[Sales]), ALL('Table'[MANUFACTURER])