Bucket items into two groups

[Brian_B]

Hello new to PowerBI and Dax and trying to convert one of my old Tableau reports into Power BI but i am having the hardest time to figure out Bucketing in the sense i need.   (tableau Sets)

i have Expense Reports based on Line amounts that i need to bucket into Over $200 and Under $200.  The data set can have multiple Expense Reports per line since its transaction line based.

i need to be able to SUM each Expense report based on row and bucket it into either category.  

exp123  $70

exp123  $30

exp123 $40

exp435 $25

exp564 $30

based on this i would like exp123 to be in the over200 bucket and the other two in under 200

Over 200 = If(SUM('Expense Report Analysis'[Line Amount]) >= 200, "Over", "Under") 

i can achive this if i use a table and then also bring in Expense report number as value but once i remove the expense report number i get one total line over 200

i have tried Over 200 = SUMx(Values('Expense Report Analysis'[Exp Rpt Number]),
IF(sum('Expense Report Analysis'[Line Amount] )>= 200, "Over", "Under"))  but i get the error SUMX cannot work with values of string type.     neither column reference is text they are whole numbers unformatted 

but i need to be able to do a high level analysis that i can count the number of Expense reports in each bucket. 

thanks! 

[MattAllington]

try this

Over 200 =
IF (
    SUMX (
        VALUES ( 'Expense Report Analysis'[Expense ID] ),
        CALCULATE ( 'Expense Report Analysis'[Line Amount] )
    ) >= 200,
    "Over",
    "Under"
)

You can read about SUM vs SUMX in Power BI at my blog.

[Brian_B]

Thank you however this is still still doing the same thing.   When i have the expense report listed as a value it will display if the report is over or under but once I remove the expense report it will just give me a sumed amount of all expense reports which is always Over.  

Still looking for a way to show Counts and Dollars of each bucket

[MattAllington]

You will need to post a sample workbook as I can’t tell what the issue is from what’s you have described. 

[Brian_B]

Matt, Sorry if its hard to understand however i have created a very basic .pbix file with expense reports and line amounts.

attached image is the the tableau verison i need to recreate in power bi.  

over under  - link to .pbix

BTW i feel special that you repsonded because i am starting "learn to write DAX"

[MattAllington]

Try this

Total Over 200 =
CALCULATE (
    SUM ( 'Expense Reports Analysis - Yale'[Line Amount] ),
    FILTER (
        VALUES ( 'Expense Reports Analysis - Yale'[Expense Report] ),
        CALCULATE ( SUM ( 'Expense Reports Analysis - Yale'[Line Amount] ) >= 200 )
    )
)

[Brian_B]

Matt

This is a great much closer to what I am looking for. 

Issue 1.  i was able to create a under 200 measure to match, not ideal as i would like one calc but it works. However, i am running into the issue where i cannot use these measures in many visualizations like a matrix.   This calc appears to be totaling as I would expect. 

 Issue 2. I attempted to use the same calc you provided to create a calc column (so i can use in a viz) but the calc is not behaving the same way. The totals are different (not totaling lines per expense report). I can tell this just by looking in the data view. Also when i tried to recreate the under 200 i get circular dependency error.  

Issue 3.  How can this be modified to show a distinct count of reports (not the sum as it now)

thanks for your time and help!

[MattAllington · Accepted solution]

I don’t understand what you mean by issue 1. Can you post a picture. 

Issue 2. Why do you need a column?  Yes, this measure won’t work as a column. 

Issue 3. Do you mean the last measure I wrote

Total Over 200 =
CALCULATE (
    DISTINCTCOUNT ( 'Expense Reports Analysis - Yale'[Expense Report] ),
    FILTER (
        VALUES ( 'Expense Reports Analysis - Yale'[Expense Report] ),
        CALCULATE ( SUM ( 'Expense Reports Analysis - Yale'[Line Amount] ) >= 200 )
    )
)

Another tip. You should simplify your table names and column names. It makes writing DAX much easier, and easier to understand. Eg

Total Over 200 =
CALCULATE (
    DISTINCTCOUNT ( Expenses[Expense ID] ),
    FILTER (
        VALUES ( Expenses[Expense ID] ),
        CALCULATE ( SUM ( Expenses[Line Amount] ) >= 200 )
    )
)