Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a table called Billing Info which has columns:
I would like to work out the Average GP for Fixed Monthly Rate per Year-Month.
I would need to sum the total GP per UserName per Year-Month then work out the average over that.
Example:
Solved! Go to Solution.
Hi @rush,
From the error message, column [Gauteng] is TEXT data type. And you have specify the aggregation (eg: SUM) for this column in the measure while this aggregation doesn't support TEXT values. So please check the [Gauteng], see if it can be convert to number.
By the way, you can upload your .pbix file to OneDrive or Dropbox and share the link here.
Best Regards,
Qiuyun Yu
Hi @rush,
Measure1 = IF(MAX('Table1'[BillingType])="Fixed Monthly Rate", CALCULATE(AVERAGE(Table1[GP]),FILTER(ALL('Table1'),'Table1'[BillingType]="Fixed Monthly Rate" )),BLANK())
Measure 2 = CALCULATE(AVERAGE(Table1[GP]),ALLEXCEPT(Table1,'Table1'[UserName]))
If above DAX doesn't meet your requirement, please share screenshots about the desired results for our analysis.
Best Regards,
Qiuyun Yu
Thank you @v-qiuyu-msft
It does not bring back my desired results.
Please see below of the final result:
Table desired results:
Hi @rush,
As your sample data you provided doesn't contain StaffRegion while the desired results have it, you can try the below DAX:
Measure = CALCULATE(AVERAGE(Table1[GP]),FILTER(ALL(Table1),'Table1'[Year-Month]=MAX('Table1'[Year-Month])),VALUES(Table1[StaffRegion]))
If it doesn't work, please share corresponding sample data. If possible, please share the .pbix with us.
Best Regards,
Qiuyun Yu
Hi @v-qiuyu-msft, thanks but it gives me an error on the text to type number.
I will update the sample file as I cannot share the pbix file.
It throws back an error:
Hi @rush,
From the error message, column [Gauteng] is TEXT data type. And you have specify the aggregation (eg: SUM) for this column in the measure while this aggregation doesn't support TEXT values. So please check the [Gauteng], see if it can be convert to number.
By the way, you can upload your .pbix file to OneDrive or Dropbox and share the link here.
Best Regards,
Qiuyun Yu
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |