calculate sum

7 Topics

How to sum a column when pulling data from semantic model?
Hi I created a simple 2 column table on my powerbi desktop using "Table" in the visualizations. I dragged in a measure column [ sale_date ] and a dimension column [widgets]. The dimension column I just want to get the sum of the whole column. I cant use powerquery due to security reasons, so all I have is powerbi desktop and I want to hopefully create a measure using DAX so I can take the sum of [widgets] column and make it available via a measure, so I can graph the answer. When I try to do a =sum(<table.[column]) I cant seem to get it to work. I'm strugging to understand whats wrong as Ive been given this task, but not a lot of training. Any help would be really appreciated. 🙂
Solved
Anonymous
1 year ago Place DAX Commands and Tips
802Views
0likes
3Comments
CALCULATE SUM for all UserIDs when UserID text is contained in the string value of another column
Hi, I'm trying to CALCULATE the SUM of meeting hours for each userID, by creating a new table from a reference table. However, I have a column for primary userID, and a separate column for secondary userIDs, which is a string of comma separated userIDs. I can do it easily for the primary userID column, but I'm unable for the secondary userIDs, probably because I need to search the value for text by iterating through each userID. I tried transforming the secondary userID column to expand into additional rows and separate the comma-separated string, but then that I would have to calculate the sum of distinct values for the primary userID column, and can't find a way to do that either. 'Meeting Hours' table MeetingID MeetingHours PrimaryUserID SecondaryUserID 1 1 10 11, 12 2 0.5 11 10,12,13,14,15 3 1 12 13 4 0.25 13 10, 12 5 0.5 14 10, 13 6 0.5 15 11,14 'Summary' table PrimaryUserID PrimaryMeetingHours SecondaryMeetingHours 10 1 1.25 11 0.5 1.5 12 1 1.75 13 0.25 2 14 0.5 1 15 0.5 0.5
Solved
cescovedo
1 year ago Place DAX Commands and Tips
1.1KViews
0likes
7Comments
Help with Running Sum Measure Using Virtual Tables and Rank in DAX
Hi everyone, I'm trying to create a running sum measure in DAX that changes dynamically based on the applied filters. The running sum should accumulate values according to the rank of each row. I've already created separate tables, and they work as expected. However, when I attempt to implement this using a measure with virtual tables, it doesn't produce the desired result. Here's the code I'm working with. Each part seems to work independently (e.g., ranking and filtering), but as a combined measure, it fails to return the correct running sum per rank. Running_sum_measure = VAR tbl_1 = ADDCOLUMNS( FILTER(ALL(sales_table[CodigoArticulo]),[CQ1]>0) ,"porc" ,[PorcentajeParticipacionCQ1] ) VAR tbl_1_sorted = ADDCOLUMNS( tbl_1, "Rank", RANKX( tbl_1, [porc] + RAND() * 0.0001, // Adding a slightly larger random component to ensure uniqueness , DESC, Dense ) ) VAR table_rank = ADDCOLUMNS( tbl_1_sorted, // Columna "RowNumber" para asignar un número de fila único a cada registro "RowNumber", RANKX( ALL(tbl_1_sorted), // Considera todos los registros de la tabla sin filtros [Rank] + RANKX( ALL(tbl_1_sorted), CALCULATE( MAXX(tbl_1_sorted, tbl_1_sorted[CodigoArticulo]) // Valor máximo de CodigoArticulo para el desempate ), , ASC, Dense // Usa Dense para clasificaciones consecutivas ) / COUNTROWS(ALL(tbl_1_sorted)), // Ajuste para evitar empates basado en el total de filas , ASC, Dense // Usa clasificación ascendente y consecutiva para "RowNumber" ) ) VAR final_ = ADDCOLUMNS( SUMMARIZE( table_rank, table_rank[CodigoArticulo], table_rank[RowNumber], "porc", SUM(table_rank[porc]) ), "RunningSum", VAR CurrentRank = [RowNumber] RETURN CALCULATE( SUM(table_rank[porc]), FILTER( ALL(table_rank), table_rank[RowNumber] <= CurrentRank && table_rank[CodigoArticulo] = [CodigoArticulo] ) ) ) VAR running_current = SUMX(final_,[RunningSum]) RETURN running_current the initital table will look something like this CodigoArticulo porc abc234hj21 0.001269819292983 bc234hj21 0.023401928347459 bc234hj223 0.032123394040404 bc234hj24gn 0.011001110932900 If anyone has experience with handling ranks with tie-breakers in a measure like this, I’d appreciate any advice on how to make this running sum work dynamically with filters and rankings. Thank you in advance for any help you can provide!
Solved
PipeRey
1 year ago Place DAX Commands and Tips
1KViews
1like
1Comment
How to calculate a simple sum of [measure] to use in a % of total calculation
Hi. I would like to have a calculation to show my total sum of [Daily Badge Counts] (168431). My attempt to show this is in my [test2] calc below. [Daily Badge Counts] is a measure. Ultimately, I want to calc a % of grand total. I have the following table to show my existing data. Here is my [test2] meaure. test2 = CALCULATE( [Daily Badge Counts], all(Badge[BADGE_SCAN_DAYOFWK_TXT])) Daily Badge Counts = CALCULATE(DISTINCTCOUNT(Badge[Badge&Date ID])) Thanks
Solved
FOXYBARK
3 years ago Place DAX Commands and Tips
884Views
0likes
2Comments
measure is returing wrong total value in table visual
hi everyone i have created a measure that calculates the sales agents incentive. it's returning value per row correct but the total value in the table visual is wrong the measure is : new agent incentive = CALCULATE([new commissin sum]*max('Agent Rate Key'[Agent Rate])) new commission sum measure : new commissin sum = Sumx(Summarize('categories trans','categories trans'[categories summarize],'categories trans'[base type without zeed], "_1", [new commission calculation]), [_1]) new commission calculation measure : new commission calculation = IF([new achiev %]<= 0.8999,0, IF(([new achiev %]>=.90 &&[new achiev %]<=.9499),300-(.1*300), IF([new achiev %]>=.95 &&[new achiev %]<=.9999,300-(.05*300), IF([new achiev %]>=1 &&[new achiev %]<=1.09,300, IF([new achiev %]>= 1.1 &&[new achiev %]<=1.1999,300+(.1*300), IF([new achiev %]>=1.2 &&[new achiev %]<=1.29999,300+(.2*300),IF([new achiev %]>=1.3,300+(.3*300),0)))))))
lawadaa
3 years ago Place DAX Commands and Tips
954Views
0likes
5Comments
measure is not showing total value in table visual
hi everyone i have created a measure that calculates sales agents commission based on his achievment % for each sales category. when i added the measure to a table visual, it's showing the the total value of the table visual as 0 instead of summing the commission values for each category. the total for measure 'new commission calculation' shoulde be 660 instead of 0 and the total for measure 'new agent incentive' should be 225 instead of 0
Solved
lawadaa
3 years ago Place DAX Commands and Tips
639Views
0likes
1Comment
Calculate Sum when lookup value is in search colu
Hello, I want to calculate the SUM of the ratio for each month. What I would expect is value 138,26 in every row in column ‘Sum of Ratio’ when month = 1. And another value that corresponds to the sum of that month. When I hardcode value 1 in the fitler it works (but that's not the solution ofcourse 😉 -> FILTER(dimCalculations, dimCalculations[Month] = 1) My approach comes from excel, but I guess that doesn’t work here. Can anyone help me in the right direction? Kind regards, Steve
Solved
SteveMBSDO
3 years ago Place DAX Commands and Tips
874Views
0likes
3Comments