Forum Discussion

Brian_B's avatar
Brian_B
New Member
7 years ago
Solved

Bucket items into two groups

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! 

Need Help
  • MattAllington's avatar
    MattAllington
    7 years ago

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

     

7 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    7 years ago

    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's avatar
      Brian_B
      New Member
      7 years ago

      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's avatar
        MattAllington
        Community Champion
        7 years ago

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