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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.