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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors