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
PaulPed4
Helper II
Helper II

Average not calculating correctly

I have the sum of the line items per PO and now need to get an average of those totals. When I place the field in a Card and specify for it to be presented as an average, it gives me an incorrect answer. When I export the table with PO numbers and totals for those POs I get the correct average which is lower than the Card.

 

Now if I create the below Measure the average is calculated correctly. Why?

 

Average PO Spend = AVERAGEX(SUMMARIZE(Table, Table[PO], "to Average", [Spend Measure]), [Spend Measure])

 

Spend Measure = CALCULATE([Total Spend], FILTER(Table, Table[PO]))

7 REPLIES 7
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @PaulPed4,



I have the sum of the line items per PO and now need to get an average of those totals. When I place the field in a Card and specify for it to be presented as an average, it gives me an incorrect answer.


It seems that you want to display the average measure in card visual but it shows the incorrect average value.

 

What dax expression do you use for that average measure which gives you incorrect value?

 

If it is convenient, could you share your a dummy pbix file which can reproduce the scenario and your desired output, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Cherry,

 

DAX expressions are mentioned above.

 

Regards,

Paul

Hi @PaulPed4,

 

If it is convenient, could you share some data sample and your desired output so that I can understand your scenario better and get the solution more quickly.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ragnarock1982
Frequent Visitor

Hi,

 

I'm sure I've read somewhere that the average on a 'Card' uses the Median average. . whereas the 'Averagex' calculation will use Mean average.

 

I could be wrong though Smiley Happy

 

Maybe try all the average 'types' in an excel formula and see which one matches the Card value?

I'm using a Card to present both calculation answers and getting different values for both.

 

You can specify Average, Median, etc... on a Card so that's not the problem. The Measure value can't be manipulated in the Card but provides the correct answer.

 

When I try Median on the Card calculation it is closer, but still not correct.

Is the 'Value Decimal places' option, under 'Data label' on both cards set to 2?

 

I can only think that your DAX calculation is using 2 decimal places(or more depending on the original data), whereas your other card is defaulting to 0 decimal places.

 

Although this may not make a massive difference, it could adjust the end figure slightly.

Nope, both are set as currency in the Data Model so have the same decimal places.

 

The correct average is $166.84 and the Card is giving me $265.00 for Average and $142.61 for Median.

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.