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,
SUM rounded numbers
I need to adjust my pwd calculation By law here in my country, factories need 5% of employees to be pwd. Until then the formula below was working normally, and everyone was happy: PWD HC Goal = [Total HC] *5/100 [Total HC] is a simple COUNTROWS from the employee table. However, I've been told that fractions should always be rounded up, and this is causing a problem in the formula when we try to see the grand total. Factory Total HC 5% RoundUP 1 85 4,25 5 2 139 6,95 7 3 136 6,8 7 4 209 10,45 11 5 36 1,8 2 6 213 10,65 11 7 7 0,35 1 8 167 8,35 9 992 49,6 53 SUM 50 RoundUP For each factory, the formula PWD HC Goal = ROUNDUP([Total HC] *5/100,0) works. However, if I try to see the total, the final number is not the sum of the rounded numbers, but the rounding of the summed numbers. Which logically makes sense, but it's not what I need. How can I adjust this? Best Regards,Sum a row using visible columns
I have created a table that allows a user to choose what data columns they wish to see (via field parameters). There are up to 4 possible values they can select from. I need to get a dynamic total across each row based on those selected columns. So, for example, if they choose all 4 severity levels, the first row would total 10, second row would be 3, etc. But if they chose only Sev2 and Sev4 (meaning only those 2 data columns would be visible), they would get 4 in the first, 2 in the second etc. There are other values that can be calculated as well (such as year over year percent of change and so on) but those would obviously be calculations similiar to this basic one. What formula would acheive that result?
DAX Measure - Group Total in each customer number row
Hi, I have a seemingly simple question, however, I just can't seem to get it right. My question is the following: I have customers divided into CSAC3 groups. So a customer belongs to a specific CSAC3 group. I have also created a measure that calculates the turnover TY. However, I now want to have a measure that adds up the turnover of the CSAC03 group and displays it behind the relevant customer number with the associated CSAC03 group. What I keep getting now is that it filters this turnover by customer number each time anyway. Ultimately, I want to calculate further using this turnover per CSAC03 group. But I can't get this to work. Below is an image of the table with columns Customer number, CSAC03 and Turnover MAP TY. This is on a customer number basis. Now I actually want another column next to it with Turnover MAP TY (CSAC03). So all the customer numbers with CSAC03 ‘4PLUS’ should include the total of this CSAC03 group. Furthermore, the code of the measure, perhaps this also has something to do with it. The tables are connected by means of customer keys/customer numbers. If anyone can help, please do, thank you!
SUM and Percentage measures
Relatively new to DAX and hitting a bit of a brick wall so hoping for some help from the community! I've been through previous posts and have attempted to apply that to what I am doing, but I'm not able to get the outcome that I was hoping for! I have a table "table1" with the following fields Acorn_type_e6 provides a numerical value (between 1-65) to each postcode. I have created a new table ("table2") which has a new column "ACORN TYPE" with numbers 1-65 I now want to add 2 new calculated columns: 1) Shows the total number of occurances for the column value "ACORN TYPE" within "acorn_type_e6" (so this would be a countif in excel) 2) Shows the percentage that "acorn type" is of the overall total Its probably something really basic here, but I'm struggling! thanks in advance
I don't understand why a stock graph isn't categorizing by deposit
I have a Stock table called "S Stock" in which I have the column [tipodeposito] (which is the type of deposit) and [deposit] (which is the deposit per se). I created a Stock measure that only took into account some of the types of deposits and deposits. However, when I make a table in the report and categorize it by type of deposit, it shows the entire stock without differentiation. I have made this two ways (keep in mind [cantidad] in quantity): 1. Stock = CALCULATE(SUM('S Stock'[cantidad]), OR('S Stock'[tipodeposito] = "Consignado", OR( 'S Stock'[deposito] = "ALMACEN PYH", OR ('S Stock'[deposito] = "BODEGA PYH", 'S Stock'[deposito] = "Casa Big Sur")))) In this case, you should know all those examples of [deposito] are just a share of a [tipodeposito] that is called "Bodega" (which encompasses other deposits and doesn't intersect with "Consignado"). However, when I make the bar graph and try to differentiate the stock by [tipodeposito], this is how it appears: 2. In this case, I created two measures in order to better differentiate the stock I want to take into account from [tipodeposito] = "Consignado" and from [tipodeposito] = "Bodega". So: Stock Bodega = CALCULATE(SUM('S Stock'[cantidad]), OR('S Stock'[deposito] = "ALMACEN PYH", OR('S Stock'[deposito] = "BODEGA PYH", 'S Stock'[deposito] = "Casa Big Sur"))) Stock Consignado = CALCULATE(SUM('S Stock'[cantidad]), 'S Stock'[tipodeposito] IN { "Consignado" }) Later, I sum them up in the Stock measure: Stock = CALCULATE(Stock[Stock Bodega]) + CALCULATE(Stock[Stock Consignado]) And this is the graph I get (even weirder, as the "Bodega" has all the stock available and "Consignado" just a part of it, them being a simple column which categorizes all the stock in two categories): How can I have a graph in which the total stock is divided into these two categories, but in which "Bodega" only has those three deposits I mentiones ("ALMACEN PYH", "BODEGA PYH", "Casa Big Sur"), and without using filters in the table, just in the formula? Thanks!SUM & SUMX causing different results, how do I resolve
I have a measure for Forecasted_at_completion within my datamodel. I noticed that the figures were right in the columns of my Matrix visualisation but when I looked at the totals they were incorrect. When I use the sum function within my 'FORECASTEDTOCOMPLETIONWEIGHT' measure i get the correct value (0 in the example below), but when i use SUMX in 'TotalFORECASTEDTOCOMPLETIONWEIGHT' it returns 15.40. I've tried debugging with chatgpt but no joy. Please help. My Forecasted_at_completion in the below should show 49.21 FORECASTEDTOCOMPLETIONWEIGHT = IF( [SumBudgetSUPPLYWEIGHT] <> 0, MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0), 0 ) TotalFORECASTEDTOCOMPLETIONWEIGHT = SUMX( DISTINCT_LEVEL_ELEMENT_TABLE, IF( [SumBudgetSUPPLYWEIGHT] <> 0, MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0), 0 ) ) FORECASTED_AT_COMPLETION = SUMX( DISTINCT_LEVEL_ELEMENT_TABLE, [SumInvoiceWEIGHT] + [SumScheduledFORECAST] + IF( [SumBudgetSUPPLYWEIGHT] <> 0, MAX([SumBudgetSUPPLYWEIGHT] - [SumInvoiceWEIGHT] - [SumScheduledFORECAST], 0), 0 ) )
How to aggregate in a measure without actually aggregating?
HI! so I am trying to use the isinscopre function in a matrix to show different transaction count values based on the hierarchy of Event Name -> Offer ID -> SKU. My measure works fine to switch teh values betweek sku and offer, but for osme reason it's showing the total sum of ALL events and not just the associated event. I'm wondering if it's something wrong with my model? Below is the measure TY Transactions = VAR sku = SUM('Consolidated SKU Sales'[Total transactions]) VAR offer = SUM('Consolidated Offer'[ty trans count]) VAR event = SUM('Consolidated Events'[ty trans count]) RETURN IF( ISINSCOPE('Consolidated SKU Sales'[SKU]), sku, IF( ISINSCOPE('Consolidated SKU Sales'[Offer Name Tx]), offer, IF( ISINSCOPE('Consolidated SKU Sales'[Event Nm]), event ) ) ) So from left to right, this is my transactions measure, the count for the event, the count for teh offers for that event (one offer in this example), and the count for differnet sku products. Looking across the rows, it works fine except for that event, which is showing the sum of total counts for all. The event, sku,and offer tables are connected with unique keys, and there are unique values of the event id, so i'm not sure why this isn't calulating correctly. Is there a way to just not use Sum at all in my measure, and have it display the correct associated count? Thanks!
