summarize rows based on a row specific measure like rankx
This is what I need: The sales amount % of items in the top 20 against the total sales. This must be dynamic, the top20 this week is different from the top20 last week. So I create a measure: [sales rank] = RANKX(ALLSELECTED('DIM-Item'),[Sales amt.]) this gets all items ranked based on the applied filters like week or item category. Now I want to calculate the sales amount of the items with rank < 21 SUMX( FILTER('DIM-Item', [sales rank] < 21), [Sales amt.]) So far so good. Only items in the top20 (rank < 21) get a value in the table. The problem is the total line. The total line show the full sales amount. This is because the [sales rank] in the total line = 1. It does not filter the item lines and then summarizes. It calculates the formula on the total line and then the filter for [sales rank] < 21 is not correct. Please check this table, especially the total line: Thanks for the help Kind regards, Johan
Help me build a SUMIF funtion in powerBI
I would need to replicate, in a new column, a very simple SUMIF function. I've tried for hours, but i'm unable to complete this task in PowerBI. My database (DB_CORP) has, for each observation a single client name and 3 columns: "CUSTOMER" (the customer name); "GROUP" (the holding name of the customer, multiple customers can have the same GROUP name); "REVENUE" Below a screenshot of the new column formula I need to replicate. If a Group's revenues are negative, put a "Y" for each customer part of that group, and viceversa. ve
Calculate Sum of weekly value only if previous week was negative
I am trying to do a running sum af resource availability. But if a resource is over-used the previous week, I would like to subtract it from the current week. If it is under-used I do not want it to carry forward. For instance: Capacity 14 14 14 14 14 Orders 12 16 13 12 10 Available 2 -2 -1 1 4How to sum cells which are also present in mapping table?
Hi all, How do I create a measure that does the following: Sum table 1[column A] for all rows where table 1[column B] is equal to a value in table 2[column C] Right now I am writing it as: Measure 2 = Calculate(SUM(Table1[ColumnA]), Table1[ColumnB]=Table2[ColumnC]) But PowerBI is returning an error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression." In Excel, I would write this as: SUMIF(ColumnB, Table2'ColumnC', ColumnA) Please help. Thanks!SUMIF in DAX with filter - new measure
Hello! I'm creating a measure called Adj New Biz and I would like it to function as such: SUMIF (Project_Group_ID = NBIZ) OR ((Project_Group_ID = INT) AND CategoryName = "Marketing/New Business" or "Business Development" So you see I'm just making sure all of INT's hours that were related to new business and business development are added into everything that is under project group ID NBIZ. Not sure of an easier way to do this. I do not want to make a table, but would rather have a measure. Still pretty new to DAX. Any advice? Am I asking for two much from a measure? Should I try and code a table instead?
