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
DSQU
Frequent Visitor

Row Groupings for Measure Not Showing As Expected

I am trying to group the results of my measure into buckets with a dax SWITCH statement, but i can't seem to get it to work. 

I would expect it to put the Daniel account into bucket 1 and the Robert account into bucket 2

 

DSQU_0-1681841361799.png

 

 

I've had to slowly build my measure to work so this is the layers, starting at the begining: 

I have data that looks like this: 

DSQU_2-1681841113179.png

 

 

 

My first measure looks like this: 

 

 

 

PrincipalBalDailyFirst = 
CALCULATE ( MIN ( AccountsHistory[Balance Daily] ), FIRSTDATE ( AccountsHistory[Date] ) )

 

 

 

Second measure

 

 

 

PrincipalBalDailyLast = 
CALCULATE ( MIN ( AccountsHistory[Balance Daily] ), LASTDATE ( AccountsHistory[Date] ) )

 

 

 

 

Difference: 

 

 

 

PrincipalBalLastVsFirst = ([PrincipalBalDailyLast]) - [PrincipalBalDailyFirst]

 

 

 

 

This simply calcualtes for each account, depending on the date range slicer selection, the first balance, the last balance, and the change in balance.

 

I had to use this measure to get my totals in the pivot table to work, which finally seem to:

 

 

 

Balance Change = 
SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] )

 

 

Test measure: 

Test = SWITCH (
    TRUE (),
     SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] ) < 0, "1",
     SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] )>= 0 && SUMX ( VALUES ( AccountsHistory[Account Num] ), [PrincipalBalLastVsFirst] ) <= 260000, "2",
    "3"
)

 

Now I just want to bucket the results for the Balance Change measure for each account, into buckets. For example, if the change was more than 1 million, call it "greater than 1 milllion balance change" and it can drill down all the accounts in the matrix. 

 

Any help would be appreciated, thanks! 

 

Dave 

0 REPLIES 0

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.