Add %age values
I have 4 different percentage values, coming from 4 calculated measures, they are decimal numbers formatted as percentages, I am trying to sum all 4 of them but unable to and am getting the following error - This the formula that I'm using - Index = [weighted_float_key_service_appid_tag] + [weighted_float_savings_plan_util] + [weighted_float_key_service_reserved] + [weighted_float_savings_plan_coverage]
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
Calculate percentages with filters and ponderate values
Hi community. I have a table to which I want to calculate percentages depending on the filters I apply. The conditions for the filters are the columns “Nombre de la Organización, “Atributo”, “Ficha peguntas.EAS”. The value on which I want the percentage to be calculated is the column “Valor ponderado”. The two characteristics that I have not been able to resolve are: Not all records in “Valor ponderado” have the same values but can be 0,1, 2 or 3 so a simple formula of adding the values and dividing by the number of values does not work for me to calculate the percentage. The other point is that there are records that have no response (null) so they should not be included in the percentage calculation. I would like to know how to calculate the percentages to consider the records with different values (0, 1, 2 or 3) and not to consider the null values for the calculation. The result would have to be that when you filter by “Nombre de la Organización, “Atributo”, “Ficha peguntas.EAS” the percentages are calculated. Thank you very much. Regards Table with data
Total Is Correct but row is not when using ALL
Biggest puzzler yet: I have this measure called [%Need]. The sum total of these percentages needs to be 1 in order to compute correctly. However, it's not, so in order to correct it I need to do a formula like: [%Need]/Sum [%Need] *1. So in order to get sum portion I did: CALCULATE(SUMX(VALUES('Calendar'[calendar date]),[%Need]),ALL('Calendar'[CalendarDate])) This puts 1(I checked it out to 15 0s 1.00000000000000 in each row instead of the correct amount which is 1.0147. 1.0147 however appears in the Total at the bottom. I need 1.0147 in each row so I can have it divided by [%Need]. This probably doesn't need to be included, but just in case the [%Need] measure is: IF([CALendarDATE]>LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds]),[PYGA]/([PYALLGAs]-[GAsLyMAXDateAll]),Blank()) Any idea how to get the correct total in each row?
How to use a result of a measure in another measure
Hi there, It's my first post here. I hope this will find you well. I've created a measure which calculates the percentage of the costs. Now I want to use these percentages to calculate the Turnover per employee according to their costs. How can I use the result of a measure in another measure? Cost in % = VAR CurrentOrder = Values(expensepostings[order_id]) VAR CurrentSuborder = Values(expensepostings[suborder_id]) VAR TotalCosts = SWITCH( TRUE(), ISINSCOPE(employees[display_name]), CALCULATE(SUM(expensepostings[cost_amount]), ALLSELECTED(expensepostings), expensepostings[order_id] IN CurrentOrder, expensepostings[suborder_id] in CurrentSuborder), ISINSCOPE(expensepostings[suborder_id]), CALCULATE(SUM(expensepostings[cost_amount]), ALLSELECTED(expensepostings), expensepostings[order_id] IN CurrentOrder), ISINSCOPE(expensepostings[order_id]), CALCULATE(SUM(expensepostings[cost_amount]),ALLSELECTED(expensepostings)) ) RETURN IF( ISINSCOPE(expensepostings[order_id]), DIVIDE(expensepostings[Gesamtkosten], TotalCosts), 1 ) Depending on which level I am this measure will calculate the percentage of costs according to the order_id, the suborder_id or the employee name. The result on employee level is shown here: Now I have the percentages from each employee per suborder. I want to use these percentages to calculate the turnover for each employee per suborder. In the order table I have the information that suborder 19790 has a turnover of 875. But I don't know how to use the calculated percentages. I would like to excpect the turnover for each employee per suborder like this: Does somebody has an idea how to do this?
2% difference when calculating Percentage between 2 columms
Hi, I have the following where i have selected a value from a customer dropdown and then when i select this and calculate the percentage using a measure it is out by 2% everytime. How can i resolve this Count % = DIVIDE(DISTINCTCOUNTNOBLANK(Table[Column1]), (DISTINCTCOUNT(Table[Column2]))) *100 So there is (11/63)*100 is 17.46% but the Measue is giving 19.23% Do i need to include an AllSelected or something as i am using the filter?Add New Dynamically Updating Column To Data
Hi, I'm relatively new to using DAX in PowerBI and have turned to ChatGPT for some help but it's repeatedly giving me 2 solutions, neither of which work! The Data: I have just one data table of inbound calls into a call centre. Columns include: StartDateTime, Agent Name, Caller Number (among others). Inbound calls can be answered by different agents, so a caller number may appear 20 times on 20 different datetime occasssions and the caller may have spoken to 5 different agents. StartDateTime AgentName CallerNumber 02/09/2023 19:01 Bob Caller01 02/09/2023 19:15 Alice Caller01 07/09/2023 15:33 Alice Caller02 13/09/2023 12:04 Bob Caller01 I have created some calculated fields including: MinCallStartDateTime (returns the lower value of the date filter applied to the dashboard). MaxCallStartDateTime (returns the upper value of the date filter applied to the dashboard). MaxCallsMade (returns count of caller number based on date filters applied to dashboard). FirstCall (returns the datetime of the first call from the selected Caller Number based on the date filters applied to the dashboard). For Caller01 MinCallStartDateTime 01/09/2023 MaxCallStartDateTime 15/09/2023 MaxCallsMade 3 FirstCall 02/09/2023 19:01 What I want to achieve: 1) I want to know which agent answered the FirstCall "FirstCallAgent" - my dashboard currently returns the FirstCall PER agent (for each Caller Number Selected), I just care about who was the first agent to answer the very first inbound call (dependent on date filters applied to the dashboard). 02/09/2023 19:01 Bob 02/09/2023 19:15 Alice (this currently shows but I don't care about it) If possible it would be ideal to create a new column called "CallTally" to the table which can be updated to count each inbound call from that number dynamically to update based upon any date filters applied to the dashboard. StartDateTime AgentName CallerNumber CallTally - may change if date filter changes 02/09/2023 19:01 Bob Caller01 1 02/09/2023 19:15 Alice Caller01 2 07/09/2023 15:33 Alice Caller02 1 13/09/2023 12:04 Bob Caller01 3 2) "RepeatCallPerc" - The goal is to find out which agent is best at resolving customer problems meaning there will be fewer repeat calls / MaxCallsMade from Caller Number where one agent answered the very first call, relative to if another agent answered that first call (since a call can be answered by any agent). Bob 100% (100% of calls, where Bob answered the first call, called in at another time)(Caller01) Alice 0% (0% of calls, where Alice answered the first call, called in at another time)(Caller02) The best way I can think about doing this is to calculate: for each agent - SUM the total instances of CallTally= 1 (according to the new column created as part of step 1). Then divide this by the SUM of the total MaxCallsMade for caller numbers whereby the agent answered the FirstCall (regardless of which agents answered future calls) - does that make sense? Extra: 3) A third goal would be to isolate which agent answered the last call "LastCallAgent" from each caller number (meaning that agent was the one to resolve that query) - to be able to sum this up and see that calls handled by Agent X are more likely to be resolved (and therefore the customer is less likely to call in) compared to calls handled by Agent Y. Expressing this as a percentage for comparison reasons would also be useful "LastCallAgentPerc" = Count LastCallAgent / TotalLastCalls = SUM("LastCallAgent"). But again this would require updating the above table /column tallying inbound calls within the filtered date range. Bob 50% (50% calls Bob handled were "last calls" (Caller01) Alice 50% (50% of calls Alice handled were "last calls" (Caller02) Anyway, I have absolutely no idea how to begin this! Or if it's even possible. I would appreciate any insight and expertise you may be able to add to this. Or if you can add an easier way to think about this. Thanks.Dynamic % Difference Dax when filtering on non consecutive years
Hi there, I have a matrix in power BI, items in rows, years in columns and a YOY% difference measure. I can;t show actual data, so here is an example in excel of what I'm doing: Problem is I'm not always comparing to previous year. Sometimes we may want to compare 2019 Vs 2022. This works in excel: Excel calculates the difference in the filtered period. However when I do this in Power BI, when I filter it returns the previous year % difference not filtered period. In power BI I am using a matrix like this: Fruit YoY% (from quick measures) code is: Fruit YoY% = IF( ISFILTERED('Date Table'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE( SUM('Fruit'[Value]), DATEADD('Date Table'[Date].[Date], -1, YEAR) ) RETURN DIVIDE(SUM(Fruit[Value]) - __PREV_YEAR, __PREV_YEAR) ) Which works for YoY, but when I filter, eg, 2020 Vs 2022, it still shows 2022 Vs 2021 % dif. When I use the below code, it works when two filters are on, but defaults to 2019 Vs 2022: Fruit Earlier = VAR earlieryear = calculate( min ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = earlieryear) Fruit Later = VAR lateryear = calculate( max ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = lateryear) fruit % dif = var lateryear = calculate (max('Date Table'[Year]), allselected ('Date Table')) var maxyear = max ('Date Table'[Year]) var earlier_ = [fruit Earlier] return if (lateryear = maxyear, divide ([fruit later]-earlier_ , earlier_)) Ideally, I'd need top level to be YoY%, (across all years if possible but 2022 Vs 2021 would be fine), then the slice the years to get the below: So ideally it would be: 1. YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc) 2. Slicer two years (eg 2022 Vs 2020) 3. Filtered years % difference eg 2022 Bs 2020) 4. Remove slicers to return back to YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc) Any help on what DAX I need to make this work would really help!
