Forum Discussion

Helper I

5 years ago

Solved

Filter a graph without changing grand total value

Hello, I want to create a graph where I have the market share of 3 brands (values) and 10 countries (Axis).

The problem is that the % of grand total is made only one the filtered 10 countries but I want the total to be made on all countries on my database. Is it possible ?

Thanks a lot.

Finnaly found with this formula

% of Grand Total =
DIVIDE (
    SUM ( Table1[Values] ),
    CALCULATE ( SUM ( 'Table1'[Values] ), ALL ( 'Table1'[brands] ) )
)

6 Replies

veenashenolikar
Helper V
5 years ago
Hi likmalik ,
Can you share the formula where you are calculating the %'s. I will help you update it as per your requirement.

Regards,
Veena Shenolikar
- likmalik
  Helper I
  5 years ago
  Hello veenashenolikar, thankyou ! Here is my measure and then I just show it as %of grand total with right click:
  My Measure = CALCULATE(SUM(Sheet1[Column]), DATESBETWEEN(Sheet1[Date], DATE(2020,01,01),DATE(2020,12,31)))
  - amitchandak
    Super User
    5 years ago
    likmalik , Try a % measure like
    
    My Measure = divide(My Measure = CALCULATE(SUM(Sheet1[Column]), DATESBETWEEN(Sheet1[Date], DATE(2020,01,01),DATE(2020,12,31))), CALCULATE(SUM(Sheet1[Column]), DATESBETWEEN(Sheet1[Date], DATE(2020,01,01),DATE(2020,12,31)), all(Sheet1)))
    
    or
    
    My Measure = divide(My Measure = CALCULATE(SUM(Sheet1[Column]), DATESBETWEEN(Sheet1[Date], DATE(2020,01,01),DATE(2020,12,31))), CALCULATE(SUM(Sheet1[Column]), DATESBETWEEN(Sheet1[Date], DATE(2020,01,01),DATE(2020,12,31)), allselected(Sheet1)))
likmalik
Helper I
5 years ago
Please someone can help me ? I have the same problem with lines where I want to follow the MS evolution month by month but the percentage is not made on the total value.

This is the difference between what I want to get ( works on excel) and powerbi. Percentage are not the same.

Helper I

Finnaly found with this formula

% of Grand Total =
DIVIDE (
    SUM ( Table1[Values] ),
    CALCULATE ( SUM ( 'Table1'[Values] ), ALL ( 'Table1'[brands] ) )
)