Forum Discussion

bourne2000's avatar
bourne2000
Icon for Helper V rankHelper V
4 years ago
Solved

How to control the slicers?

I have a simple data which has two columns, category and amount   CategoryAmount A 787 B 7847 C 3949 A 9898 C 598590 B 34 A 24 B 454 C 4545 A 4545   Wh...
Need Help
Show and Tell
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Hi bourne2000 

    As you've noted, the built-in "Percent of grand total" calculation calculates the total based on the overall filter context of the visual (using ALLSELECTED in the DAX query generated).

     

    To calculate % of total for all Categories including those not displayed, you will need to create explicit measures and use REMOVEFILTERS or ALL.

    I recommend creating:

     

    Amount Sum = 
SUM ( YourTable[Amount] )
    Amount % of total = 
DIVIDE (
    [Amount Sum],
    CALCULATE (
        [Amount Sum],
        REMOVEFILTERS ( YourTable[Category] )
    )
)

     

    (applying a percentage format to the second measure).

     

    Does this work for you?

     

    Regards,

    Owen

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

Hi bourne2000 

 

Try this:

%GT Amount =
VAR _A =
    SUM ( 'Table'[Amount] )
VAR _B =
    CALCULATE ( SUM ( 'Table'[Amount] ), REMOVEFILTERS ( 'Table'[Category] ) )
RETURN
    _A / _B

 

output:

 

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/