Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bourne2000
Helper V
Helper V

How to control the slicers?

I have a simple data which has two columns, category and amount

 

CategoryAmount

A787
B7847
C3949
A9898
C598590
B34
A24
B454
C4545
A4545

 

When I tried to make a table and convert my amount into percentage

 

bourne2000_0-1643320104013.png

 

What do I want? If I place a filter for a category, I don't want the fields to change when I exclude a  category

 

For example, if I exclude category A, the percentages should not change for other categories

 

In that case, I need an output

 

Category B = 1.32 %, Category = 96.26%

 

However, I am getting the below output

 

bourne2000_1-1643320273836.png

 

Is it possible? can anyone advise? I don't want to change the value for a particular category if I exclude any of the categories. 

 

Please advise

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
VahidDM
Super User
Super User

Hi @bourne2000 

 

Try this:

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

 

output:

VahidDM_0-1643321124946.png

 

 

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/

 

 

Thanks a lot @VahidDM 

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.