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
Anonymous
Not applicable

Dynamic Measure that assigns a revenue grouping based on current selection?

Hello all,

 

 I create a custom column with using a switch function (below)q2.png

It works fine within the table, but when they are filtered by year, date, product type, and several other filters, they are no longer accurate as to its range. I think this is because the column created is on a per row basis, and it displays the first range from its list when summarized below.

q1.png

 

I am thinking that a measure that will dynamically change based on the current revenue total showing is superior to a calculated column. 

 

Is there a way to do this? 

 

Here is the syntax for the column created:

 

Revenue Range = SWITCH(TRUE(),
'Data'[Revenue ($k)]<500000, "<500K",
AND('Data'[Revenue ($k)]>=500000,
'Data'[Revenue ($k)]<1000000), "500K-1M",
AND('Data'[Revenue ($k)]>=1000000,
'Data'[Revenue ($k)]<2000000), "1M-2M",
AND('Data'[Revenue ($k)]>=2000000,
'Data'[Revenue ($k)]<3000000), "2M-3M",
'Data'[Revenue ($k)]<3000000, "<Over 3M")
1 REPLY 1
nandukrishnavs
Community Champion
Community Champion

@Anonymous 

 

Try to create a DAX measure and use that measure in the Table/Matrix visual.

 

Example

Revenue Range =
VAR __Revenue =
    SUM ( 'Data'[Revenue ($k)] )
VAR __Range =
    SWITCH (
        TRUE (),
        __Revenue < 500000, "<500K",
        AND ( __Revenue >= 500000, __Revenue < 1000000 ), "500K-1M",
        AND ( __Revenue >= 1000000, __Revenue < 2000000 ), "1M-2M",
        AND ( __Revenue >= 2000000, __Revenue < 3000000 ), "2M-3M",
        __Revenue < 3000000, "<Over 3M"
    )
RETURN
    __Range



Did I answer your question? Mark my post as a solution!
Appreciate with a kudos
🙂


Regards,
Nandu Krishna

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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