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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MegaOctane1
Helper I
Helper I

Help to create slicer with measures where amount is above certain value

I have an aging table. 
My data is like this:

CustomerAgingAmounts
AppleNot Due Yet4.000.000
Apple1-30 days1.500.000
Apple31-60 days300.000
Apple61-90 days200.000
MicrosoftNot Due Yet50.000
Microsoft1-30 days500.000
Microsoft31-60 days200.000
Microsoft61-90 days100.000
Google1-30 days600.000
Google31-60 days700.000
Google61-90 days400.000


I have a measure to calculate the
Overdue = CALCULATE(SUM('CustomerAging'[Amounts]),'CustomerAging'[Aging]<>"Not Due Yet")


I want to create slicers/buttons so the user can choose to show aging of customer with certain conditions:
Overdue > 2.000.000

Overdue > 1.000.000
Overdue > 500.000

If i use the filter in the report and filter the visual by Overdue is greater than 1 000 000 it works perfectly.
I created a measure like this:

Overdue > 1.000.000 = CALCULATE([Overdue],FILTER('CustomerAging',[Overdue]>1000000))
 
But it shows only this:
CustomerAgingAmounts
Apple1-30 days1.500.000
2 REPLIES 2
FreemanZ
Super User
Super User

Hi @MegaOctane1 ,

 

not sure if i fully get you, try like:

1) create a calculated table like below and plot a slicer accordingly:

Slicer = 
SELECTCOLUMNS(
    {500000, 1000000, 2000000},
    "Overdue", [Value]
)

 

2) plot a card visual with a measure like:

OverdueOverSelection = 
VAR _table =
ADDCOLUMNS(
    VALUES(CustomerAging[Aging]),
    "Amt", 
    CALCULATE(SUM(CustomerAging[Amounts]))
)
VAR _result =
SUMX(
    FILTER(
        _table, 
        [Amt] > SELECTEDVALUE(slicer[overdue]) 
            && CustomerAging[Aging]<>"Not Due Yet"
    ),
    [Amt]
)
RETURN _result

 

it worked like:

FreemanZ_0-1699886758882.png

 

FreemanZ_1-1699886781592.png

 

Sorry, I have corrected the Overdue measure.
The correct DAX formula:

Overdue = CALCULATE(SUM('CustomerAging'[Amounts]),FILTER(CustomerAging,CustomerAging[Aging] <> "Not Due Yet"))


I have created three matrix tables to show what i want to get out of this "slicer filter".
How can i get the result without filtering the visual in the filter pane?

 

Link to PBIX file on google drive 
Skjermbilde 2023-11-13 202944.png

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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