DAX total != total rows
Hi, I have an issue where the total of the rows is not equal to the total calculated in a table, let me explain what I mean: I calculate first the difference between the CA N and CA N-1 that I store in diff_CA measure and I store in Calcul_incremental measure: - if (diff_CA >0, diff_CA, 0) and I except the total to be 129 367,62 +0,00 = 129 637.62 and not 129 367.62 -263.86 = 129 103.76 What am I doing wrong here to fix the total taking into account not the values calculated with the condition ? Thank you in advance Kind regards,
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
Power BI don't return good total
Hello, I'm having a problem with the total sum of a column. I have several filters in my Power BI: Supervisor, Company, Creation date, etc. In the example I'm showing you, I've only filtered on SUPERVISOR = FME AND FEL. I have a column called "SUPERVISOR" and I want to calculate the total standard cost. I created a calculated column to get the standard cost: Column A * (Price/Lot size) The total standard cost is incorrect because Power BI isn't adding the results. Power BI calculates as 68 * (Price/Lot size), whereas I want it to add the data in the "Standard Cost" column. Thanks for your help
Calculating Total of a column inside virtual table
Greetings everyone, I have virtual table that looks like this: Each row is a product that belong to same product family (data is only partially visible here). "Revenue" and "Total Regional List" Price exist in the main Data Table. Next calculateions are virtual, used as measures. 1) I have first calculated total Revenue for this family, which is 858.317,55. 2) Next i have calculated "Level Factor Weight" for each item, which is "Revenue" / "Sales Revenue per Year by Family". 3) And finally i have calculated "Weighted Target List Price", which is "Total Regional List Price" * "Level Factor Weight". So far so good. Now i would like to calculated total or sum of "Weighted Target List Price", basically 0,06 + 0,03 + 0,00 + 0,65... (just as one see the numbers inside this table) and that is where i have problems. When i put "Total Regional List Price" and "Weighted Target List Price" in one virtual table without product numbers, they show the same result. Any hint would be very appreciated. Sincerely, PavloSales values and their targets
Hello, I have two tables: a sales table ( called 'Results' in the attached) and a table with targets ('Targets'). I would like to create a table, where I would like to see the sums for every manager. And also a column with targets for every manager, where the total is the sum of all the targets (i.e. 100) So far I can add just the targets for every manager, but I do not know how to add the target total. Please find the file attached: https://file.io/ploTUJXuWwfb
Correct Total using ALL() whilst also having blanks filtered out of visual?
Hi I'm hoping someone may have a quick solution to a problem i'm encountering whilst trying to provide % of total in a bar chart. The screenshot below shows a dummy dataset for customer feedback replicating the issue i'm encountering. The data spans over 3 days (20th Oct to 22 Oct), with a total of 25 customers, 9 of which opted not to give feedback. I'm needing to present this data as a % broken out by feedback score. I have used the 'feedback%' measure shown below, which leverages the ALL() function to clear the feedback_rating filter, providing a total customer count. You can see from the 2nd bar chart that this works when the blank feedback rating option is included in the visual. Example feedback score of 1 has 5 customer entries, 5/25 = 20%. I have been asked to remove blanks from the visual. When these are removed however, the % are incorrect as can be seen within the red bar chart, a feedback score of 1 now has a % distribution of 26%. The reason appears to be due to the Date slicer which is required in my real dashboard. As all the customers on 21st October did not leave feedback, I believe when the blanks are removed from the visual, all of these rows are removed from the underlying data, creating a new customer total of 19, resulting in incorrect %'s, feedback score of 1 now becoming 26% (5/19) as mentioned above. Any ideas on how to keep these values in the total whilst also keeping the blank entries out of the visual? Ideally I would like to solve it within a DAX measure as my existing dashboard already has a relatively complex data model, however I appreciate it may require a helper table of some sort. feedback% = distinctcount(Sheet1[customer_id]) / CALCULATE(DISTINCTCOUNT(Sheet1[customer_id]),ALL(Sheet1[feedback_rating])) Thanks for reading.Make total as the sum of the rows
Hey, I am having an issue trying to make the target only show values when the real has values. For the rows it is working, but on the total it is displaying the total value of the target despite if the real had value or not. I expected it to show 3,05 instead of 11,15. This is the measure that I have: Production Defect Target (SUn) = IF(NOT ISBLANK([Production Defect]), CALCULATE( SWITCH ( SELECTEDVALUE ( h_Units[Value] ), 0, BLANK(), 1, SUM(F_TARGETS[# Defect (m3)]), 2, BLANK(), 3, BLANK() ), ALL(F_PRODUCTION_DEFECT), F_TARGETS[Source] = "Prod. Defects" )) Any idea how can I fix this? Thank you. Best regards, AndréTotal for Top grouping in a matrix not calculating correctly
Hi, I wonder if someone can help me with the DAX for a Measure. I have a target field in a Matrix that doesn't seem to be calculating correctly. See below In the first level grouping the values are correct, but not in the top grouping or for the grand total. The Target is a running total for the month grouped by Program, so should be the sum of all the Target values in the top level group and for the grand total. The DAX I'm using is fairly simple. Uses 2 measures. [TargetWholeNumber] = SUMX(DISTINCT(DimDate[MonthName]), FIRSTNONBLANK(MeasureData[Target], 0)) This is the measure that I'm using in the matrix. [TargetWholeNumberRunningTotal] = CALCULATE([TargetWholeNumber], DATESYTD(DimDate[DateValue], "30/06")) Any advise will be appreciated. Cheers,
