CALCULATE - Explaining DAX to Excel folks (IN PERSON ONLY EVENT!! )
Writing DAX is easy, writing the correct formula is hard. Understanding the concepts of how DAX works differently to Excel is one of the biggest hurdles to Power BI adoption by the Excel crowd. Having learned DAX initially from Rob Collie's awesome (but now a little outdated) book - "Power Pivot and Power BI" it took me a while to realise that the CALCULATE function is not a superior SUMIFS, and thinking of it that way hindered my understanding of more complicated DAX functions. This session will cover how I explain to people how DAX works. Speaker profile: Wyn Hopkins is a Microsoft MVP, trainer, consultant and author of the book “Power BI for the Excel Analyst” Based in Perth, Western Australia, Wyn is the director of the Power BI and Excel consulting firm “Access Analytic”. Their YouTube channel has 48,000+ subscribers and Wyn co-hosts the Perth Power BI and Modern Excel meetup group. Once upon a time Wyn was a chartered Accountant with PriceWaterhouseCoopers but Wyn and team now spend their time delivering Power BI and Excel training along with reporting solutions for clients of all industries and sizes. NEW VENUE!!! Microsoft Office Level 10 100 St Georges Terrace Agenda: Wednesday 15th Feb 5:10pm Perth Time • 5:10pm - Welcome (doors open 5.00 ) • 5:15pm - Power BI and Excel News • 5:25pm - Main Session • 6:15pm - Close (a beer afterwards? ) Hope to see you there! Please cancel your spot if you cannot make it The session will not be recorded nor streamed on-line. This is an in-person only event.
Strange behavior in CALCULATE with USERELATIONSHIP and FILTER
I have a fact table with four main columns: “Type”, indicating whether the row is “Plan” or “Real” “Billed Date” “Ship Date” “Volume” The fact table is related to my dCalendar table through the "Billed Date" column. However, I need to create a visual that shows each day of the current month alongside the real shipped volume per day. The issue is that I’m getting two different results with my measures, and I cannot understand why. Here’s what I tried: First Attempt Volume = SUM(fact[volume]) 1ºTry = CALCULATE([Volume], USERELATIONSHIP(dCalendar[Date], fact[Ship Date])) Then, I created a table visual, placed dCalendar[Date] in the rows, and added a slicer on the “Type” column, filtering it to “REAL”. ✅ Result: This method works as expected — I get the correct daily volumes. Second Attempt (Expected to be better): 2ºTry = CALCULATE([Volume], USERELATIONSHIP(dCalendar[Date], fact[Ship Date]), fact[Type] = "REAL") However, when I use this measure in the same visual, I only get values for the days where the Ship Date and the Billed Date are the same. For all other days, the result is blank. Third Attempt (I don’t know why it works): 3ºTry = CALCULATE([Volume],USERELATIONSHIP(dCalendario[Date],fact[Ship Date]),fact[Type]="REAL") When I delete the FILTER DAX and use the “native” filter from CALCULATE, the measure works. Why??? My understanding and question: I expected that adding fact[Type] = "REAL" as a filter inside CALCULATE would behave the same as applying the slicer at the visual level — but it doesn't. Instead, it seems to further restrict the context and only shows rows where both the active relationship on Ship Date and the Type filter apply, but in a way I didn’t anticipate. Can anyone help me understand why this is happening? Is there a difference in how context transitions or row filters are applied in this scenario with USERELATIONSHIP + FILTER compared to using a slicer? Also, what would be the best practice for handling this kind of situation — should I keep relying on slicers for these filters, or is there a more robust way to incorporate the Type filter within the measure? 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
Sum only Values where another Column has Data
Harder than it looks: I have this simple table like below: I want to calculate how we're performing based on the goal in a Card. So I need to Sum only the goal values where the Gross Adds column is not blank(GAs are in the past and Goals are for the whole month). In theory this should work, but it puts a total for all the months previous rather than what is selected in the slicer. What's strange is if I replace [GAGoal] with [Gross Adds] in the measure below it correctly adds up the Gross Adds for the current month selected in the slicer. GoalTotalCurrent = VAR LastDaySelection = LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds] ) VAR CurrentRange = DATESBETWEEN ( 'Calendar'[Calendar Date], MIN ('Calendar'[Calendar Date] ), LastDaySelection ) RETURN IF ( LastDaySelection >= MIN ( 'Calendar'[Calendar Date]),CALCULATE ( [GAGoal], CurrentRange )) I've tried things like this, but it results in blank: SumGoals = IF(SUMX('Subscriber Activity',[Gross Adds])<>Blank(),SUMX('Subscriber Activity',[GAGoal])) So it shouldn't sum 1/8,1/9,or1/10 Date Goal Gross Adds 1/1/2025 5 6 1/2/2025 7 5 1/3/2025 8 7 1/4/2025 7 7 1/5/2025 4 3 1/6/2025 3 2 1/7/2025 2 1 1/8/2025 5 1/9/2025 5 1/10/2025 5How to calculate percentage of each group out of total number of distinct rows
Hello! I have a table that looking at type of insurances that customers have and customers could select more than one type, however ultimately, I would like to create a visual in my dashboard that shows the percentage of responses for each insurance type OVER the DISTINCT number of respondents. For example, if 301 people selected 'Medicaid' (this includes folks who selected only Medicaid or selected Medicaid and other types of insurance, for the numerator all I care about is that they selected 'Medicaid') out of 861 DISTINCT individuals who responded to the survey, how do I get a percentage that reflects non-distinct count per group OVER a distinct number of IDs? Here is my visual thus far: ^The percentages you see at the bottom of each square in the tree map DO represent what I am looking for, for example 301/861 folks= 34.96% selected Medicare, however I had calculated that in R before exporting to Excel and then importing into Power BI. I want to calculate this however IN Power BI if possible instead because when I try to filter based on age, educational level, etc. as seen on the right-side of the screen, those percentages do not change. Here is my table that the visual is based on- column Q1= ID of individual, column insurance= insurance type, total_insurance= distinct count of Q1 (I calculated this in R) & count_insurance=count of insurance by group /total_insurance, again calculated in R. Any suggestions for how to calculcate total_insurance & count_insurance in PowerBI rather than bringing them over from R would be much appreciated, thank you! 😊 How do I recreate these last two columns exactly as they are but using a calculation made in PowerBI rather than R?^ Attached is a PowerBI workbook at this link in Dropbox https://www.dropbox.com/scl/fi/fmtzurp0k6jkl36bk7qll/insurance_long.pbix?rlkey=i6ph2ji5ll9hwicv14yjtcg5q&st=upw9xiot&dl=0 . The table in my screenshot above is for the insurance_long table in the PowerBI workbook. The sheet_na table in the PowerBI workbook contains the ID "Q1" and the age category variable used as a filter on the far right of the visual "Q4_cat" and then there is also a table for race as that also serves as one of the filters. Thank you!
Calculate new employees per month
Hey there, I am trying to create a visual with the new employees for each month. Positions can occur several times but not at the same time. For example Position P-10057. ValidFrom ValidTo FullTimeEquivalent PositionId 31.12.2023 31.12.2154 0.2 P-10016 01.12.2012 31.12.2154 1 P-10017 18.01.2016 31.12.2154 1 P-10055 01.09.2017 31.12.2154 1 P-10056 01.02.2023 31.12.2023 1 P-10057 30.06.2024 14.10.2024 1 P-10057 14.11.2023 29.02.2024 1 P-10058 31.07.2024 31.12.2154 1 P-10058 01.08.2021 04.06.2024 1 P-10059 01.08.2022 30.07.2025 1 P-10060 01.08.2022 31.07.2025 1 P-10061 15.08.2022 13.08.2025 1 P-10062 01.08.2023 30.07.2026 1 P-10063 01.08.2023 08.05.2024 1 P-10064 01.08.2021 04.06.2024 1 P-10065 I use the following Dax: FTE_Onboarding = CALCULATE( SUM(PositionWorkerAssignmentsV2[FullTimeEquivalent]), FILTER( PositionWorkerAssignmentsV2, PositionWorkerAssignmentsV2[ValidFrom] >= MIN(DateTable[Date]) && PositionWorkerAssignmentsV2[ValidFrom] <= MAX(DateTable[Date]) ) ) The datetable goes from 2022 to 2026. So the missing FTE before 2022 are correct. However I am get the following result: FTE_Onboarding Year Month 3 2022 August 1 2023 Februar 2 2023 August 1 2023 November So the FTE from Dec 23 and the two FTE from 24 are missing. Looking forward to your answers 🙂Calculate Value to GAP Price
Hi team, I need your help to calculate "GAP PRICE", this is divide the Price Value of one column value between the another value from same column A column has 2 values: "Company 1" and "Company 2" Each Product has an Unit Price in each Company GAP_Price Product 1 = Unit Price (product 1 in Company 1) / Unit Price (product 1 in Company 2)
