Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
@lava_ Perhaps:
Measure =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE(
'Table',
[Vendor Category],
"__NumVendors",COUNTROWS(DISTINCT('Table'[Vendor Name])),
"__SumInvoice",SUM([Invoice Amount]),
"__Average",DIVIDE([__SumInvoice],[__NumVendors])
)
RETURN
AVERAGEX(__Table,[__Average])
or, assuming that you have a table with Vendor Category, it is:
Measure =
VAR __NumVendors = COUNTROWS(DISTINCT('Table'[Vendor Name]))
VAR __SumInvoice = SUM('Table'[Invoice Amount])
RETURN
DIVIDE(__SumInvoice,__NumVendors)
@Greg_Deckler your solution worked!! Thank you so much. I've spent way too long trying to figure this out. What a relief. Thank you.
Hi:
This is an interesting question. You can take a look at the attached. The measure I went with is working as follows.
If Category A had Q1 amount this year of 100 and had Q1 amount last year of 200, then the average would be 150.
You could also be asking what is the average invoice amount during the quarter. E.G, if all the days in all Q1's had 100, then 100 is the average invoice amount during the quarter. I have this as the "alt" measure.
https://drive.google.com/file/d/1qqPsL0kU6xW4WS156HO-tPL3i_mVfoU5/view?usp=sharing
@lava_ Perhaps:
Measure =
VAR __Table =
ADDCOLUMNS(
SUMMARIZE(
'Table',
[Vendor Category],
"__NumVendors",COUNTROWS(DISTINCT('Table'[Vendor Name])),
"__SumInvoice",SUM([Invoice Amount]),
"__Average",DIVIDE([__SumInvoice],[__NumVendors])
)
RETURN
AVERAGEX(__Table,[__Average])
or, assuming that you have a table with Vendor Category, it is:
Measure =
VAR __NumVendors = COUNTROWS(DISTINCT('Table'[Vendor Name]))
VAR __SumInvoice = SUM('Table'[Invoice Amount])
RETURN
DIVIDE(__SumInvoice,__NumVendors)
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |