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

[caleb1208]

Hello all,

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

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.

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")

[nandukrishnavs]

@caleb1208 

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 🙂