Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lava_
Helper I
Helper I

How 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.

 

lava__0-1652128364591.png

 

 

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 NameVendor CategoryInvoice NumberInvoice DescriptionInvoice DateInvoice Amount

Vendor 1

Category C1447

Description

1/1/2019$234.97
Vendor 1Category C3457Description2/1/2019$459.03
Vendor 2Category B123Description1/1/2019$3605.99
Vendor 2Category B654Description2/1/2019$4526.86
Vendor 3Category A28998Description1/1/2019$39408.33
Vendor 3Category A12879Description2/1/2019$23609.76
Vendor 4Category B1257Description1/1/2019$1094.32
Vendor 4Category B2146Description2/1/2019$2094.31
Vendor 5Category C4948Description1/1/2019$609.71
Vendor 5Category C5940Description2/1/2019$876.50

 

Any guidance would be appreciated. Thank you!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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)
  

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
lava_
Helper I
Helper I

@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.

Whitewater100
Solution Sage
Solution Sage

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 

 

Cat Qtr Avg Amt = AVERAGEX(VALUES(Dates[Year & Qtr]),
AVERAGEX(VALUES(Category[Category]),
[Inv Total]))

Whitewater100_0-1652184896701.png

 

Greg_Deckler
Super User
Super User

@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)
  

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors