4 / 13/ 52 Week Rolling Averages
Hi I am trying to create a number of cards that contain the following rolling averages. 4 week, 13 week and 52 week. I don't know what measure to create in order to achieve these reults. I can try and send the sample file but not sure best way to attach it. Link to sample file attached https://1drv.ms/u/s!AiZ1-kl9lagZgt4olcKdIRRuzIxiEg?e=bbrnvL Data Table Calendar Table Visual I am trying to achieve for weekly averages Weekly Sum Matrix already built
How to keep filter on category but remove it on # of units within category?
Hi there, I have a report that analyzes how much we've spent on third-party vendors. We have 26 different vendor categories that the vendors can fall under. I'm having issues with displaying the average category cost over a single year on a line chart on a drill through page. When a user drills in on a single vendor, the line chart is supposed to show the average amount spent within that selected vendor's category over a singler year (so the average would include total spent within that category divided by the number of vendors within that category). At the bottom is the formula used to determine the average category total, but this becomes challenging when a user drills in on a vendor. When they drill in, it filters on the vendor and then on the vendor's category. So the average category total runs as if there's only one vendor in that category, which is obviously incorrect. Question: How can I keep the filter on the vendor category, but remove the filter on the number of vendors in that category? For example, looking at the screenshot below, if I drill in on Vendor 1, how can I keep the filter on the category "Trading System" and still have the number of vendors be 7? Would I apply ALL or KEEP/REMOVEFILTERS somewhere in the formula below? Again, note that this information would be displayed in a line chart, not in a matrix. Category Average = VAR NumVendors = COUNTROWS(DISTINCT('Vendor Invoices_Master'[Vendor Reporting Name])) VAR SumInvoice = SUM('Vendor Invoices_Master'[Invoice Amount]) VAR TotalAvg = DIVIDE(SumInvoice, NumVendors) VAR _Table = ADDCOLUMNS( SUMMARIZE( 'Vendor Invoices_Master', 'Vendor Invoices_Master'[Vendor Category]), "_NumVendors", NumVendors, "_SumInvoice", SumInvoice, "_Average", TotalAvg ) VAR Result = AVERAGEX(_Table, TotalAvg) RETURN ResultHow to find average cost spent within a category per quarter/year
Hi there, I have a report that analyzes the amount spent on third-party vendors over the last few years. There are 26 different categories the vendors can fall under. I am trying to determine the average amount spent within a category per quarter/year and display it as a secondary Y-axis on a line chart. The line chart currently displays the total amount spent on the vendor per quarter by the year the user selects in the slicer visual. This is for a drill through page, so the user will select a vendor on the report page, which will then take them to the vendor's details on the drill through page. So the secondary Y-axis would show the average cost of the category of the selected vendor per quarter. My question is, is it possible to create a measure to find the average cost spent within a category per quarter? If so, how would I do it? It's tricky because I have to group the categories and then determine the number of vendors within each category for that quarter in order to find the average amount spent (I think this is called segmentation?). I'm just not sure if this can be done by a measure or if I need to create a separate data table to figure this out. Below is the data table I would use to pull information from, which includes sample data. The actual data table includes all of the invoices for each vendor throughout the month starting from Q1 2019 to Q1 2022. So just imagine that each vendor would have a lot more rows of invoices for each month/year. Vendor Name Vendor Category Invoice Number Invoice Description Invoice Date Invoice Amount Vendor 1 Category C 1447 Description 1/1/2019 $234.97 Vendor 1 Category C 3457 Description 2/1/2019 $459.03 Vendor 2 Category B 123 Description 1/1/2019 $3605.99 Vendor 2 Category B 654 Description 2/1/2019 $4526.86 Vendor 3 Category A 28998 Description 1/1/2019 $39408.33 Vendor 3 Category A 12879 Description 2/1/2019 $23609.76 Vendor 4 Category B 1257 Description 1/1/2019 $1094.32 Vendor 4 Category B 2146 Description 2/1/2019 $2094.31 Vendor 5 Category C 4948 Description 1/1/2019 $609.71 Vendor 5 Category C 5940 Description 2/1/2019 $876.50 Any guidance would be appreciated. Thank you!Calculate Average of Percentage in Total
Hi Can someone please help me with the Dax ? Dataset - Calculate - N - Sum of Value where Color is "Red" D - Sum of Value where Color is "Yellow" Div - N/D N = CALCULATE(SUM(Sheet1[Column3]),FILTER(Sheet1, Sheet1[Color] = "Red")) D = CALCULATE(SUM(Sheet1[Column3]),FILTER(Sheet1, Sheet1[Color] <> "Red")) Div = DIVIDE([N],[D]) The highlighted percentage in yellow needs to be the average of above percentages - it should show 89%. Can someone please help me with the DAX? Thanks in advance
DAX Formula for Weekly Average, to work with Date Range slicer
Hello Everyone, I was hoping you'd be able to help me with getting a DAX formula together. A bit of context - I have a dataset with a date table, and a table which is a list of 'Tickets issued'. They are linked via Issued Date. I am trying to get the average number of of tickets Issued a week, for each location (tickets have the location that they were issued to). I currently have the following DAX, but it isn't calculating properly (e.g. giving a greater average for a location with a lower number of total issued - see snapshot below) and I think this due to the 'Week Number' resetting at the start of each year, when my data spans 3+ years: Average Issued = AVERAGEX(VALUES( 'V_Date Table'[WeekNum]),[Total Issued]) Date Table = V_Date Table = ADDCOLUMNS (CALENDAR (DATE(2017,1,1), DATE(2025,12,31)), "DateAsInteger", FORMAT ( [Date], "DDMMYYYY" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "MM-YYYY" ), "YearMonthShort", FORMAT ( [Date], "mmm-YYYY" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "WeekNum",WEEKNUM([Date],2)) Total Issued: Total Issued = DISTINCTCOUNT(R_Issued[Reference]) [Reference] is just a unique reference code for each ticket. Snapshot of current DAX not working (for a date range of the last 6 months, e.g.): Does anyone have a way to calculate this, (or know where I am going wrong), so that it works with any date range? Any help will be really appreciated! 🙂 Many thanks, Helen
Average calculation in same column but for different values
Hello everyone, I'm trying to calculate the average of some different values in the same column. And after that, using that result to divide another value in the same column, resulting a value in porcetage. The example of the table and the equation are shown below: For ID CODE 41: X =[ 10 / (20+5/2) ] - 1 | | | TYPE--------------> A B C -> media between B and C I really appreciate for the help.
Matrix weighted averages in subtotals and total
Hi, Is there someone who can help me by the following. I have the folowing table: Table: Salesman Region Salesman Sales Budget Margin Index Margin Budget A A1 1.000.000,00 50 500.000,00 A A2 2.000.000,00 60 1.200.000,00 A A3 2.500.000,00 55 1.375.000,00 B B1 2.000.000,00 60 1.200.000,00 B B2 4.000.000,00 45 1.800.000,00 B B3 3.000.000,00 55 1.650.000,00 C C1 1.500.000,00 55 825.000,00 C C2 2.000.000,00 50 1.000.000,00 C C3 6.000.000,00 40 2.400.000,00 Total 24.000.000,00 11.950.000,00 Region A AVG Margin Index 55,91 Region B AVG Margin Index 51,67 Region C AVG Margin Index 44,47 Total AVG Margin Index 49,79 And I want to put it in a matrix like the one below: Subtotals by region and a grand total 1) Sales Budget is a SUM 2) Margin Budget is a Sum 3) Margin Index is a weighted AVERAGE (this is the one I am fighting with !!!!) Requested Visual Power BI Matrix Region Salesman Sales Budget Margin Index Margin Budget A 5.500.000,00 55,91 3.075.000,00 A1 1.000.000,00 50,00 500.000,00 A2 2.000.000,00 60,00 1.200.000,00 A3 2.500.000,00 55,00 1.375.000,00 B 9.000.000,00 51,67 4.650.000,00 B1 2.000.000,00 60,00 1.200.000,00 B2 4.000.000,00 45,00 1.800.000,00 B3 3.000.000,00 55,00 1.650.000,00 C 9.500.000,00 44,47 4.225.000,00 C1 1.500.000,00 55,00 825.000,00 C2 2.000.000,00 50,00 1.000.000,00 C3 6.000.000,00 40,00 2.400.000,00 Total 24.000.000,00 49,79 11.950.000,00
Averaging measure average results (Averaging subtotals in a matrix)
I have created measures which calculates rates and returns an average based on 3 context: location, job title and department What I need now is to also return an average of those results within the same department and location (2 context) As you can see in the image below the "Construction" Department has 3 job titles for the "IE" Location. Left hadn side table details the resulting averages. On the bottom right hand side you can also the employee breakdown by location. I need the the average of those subtotals to then populate in the main table on the left hand side as a roll-up subtotal. The end results should be 22.50 as the average of all 3 job titles in the "Construction" Department within the "IE" location. Been struggling with Dax calculations to figure this out. Some guidance would help me immensly the DAX I used to calculate the average you see in the left hand table is : Average Hourly Rate per Employee by Category = AVERAGEX('Active Employees', IF('Active Employees'[Department] <> "", [Max Hourly Rate], 0)) Max Hourly Rate = CALCULATE( MAXX('Active Employees', 'Active Employees'[Hourly Rate]))
