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!
Adjusting use relationship formula to include specific measure values on line graph
I have 2 rolling 14 day average calculations and Im hoping to display them (highlighted) on the same line graph: However I've tried to create a userelationship measure to get them on to the correct axis and I'm getting the correct numbers for Epi Count but Event count values are coming in as counts rather than the ones I need (highlighted on left below): I'm using this measure: Cumulative Rate - Event Count = CALCULATE(COUNTX('Sheet1',[Prev 14 Day Event Count]/4761865*100000),USERELATIONSHIP(Sheet1[Event Date],DimDate[Dates]),NOT(ISBLANK('DimDate'[Dates]))) Can anyone help get the Cumulative rate -Event counts on to the line graph SEE FILE ATTACHED https://www.dropbox.com/s/ksi9fm0kbzpj1hg/Notification%20vs.%20Epi.pbix?dl=0
