Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Spend Measure = CALCULATE([Total Spend], FILTER(Table, Table[PO]))
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
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
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
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |