Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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 ) ) )
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.
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
You will need to post a sample workbook as I can’t tell what the issue is from what’s you have described.
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.
BTW i feel special that you repsonded because i am starting "learn to write DAX"
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 ) ) )
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!
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 ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |