Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to create calculated columns that bring out results per Quarter / month per month based on type of caller.
For example
If Month = April and Year = 2018
and Type of caller = "email"
then sum values
I am new to PowerBI, this is what i have managed to piece together.
Any help id greatly appreciated.
Thanks,
Solved! Go to Solution.
Hi @marcus505,
Yes, here you should update your formula like this.
Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]= 555510002))
Regards,
Frank
Hi @marcus505,
Based on my test, we can use the formula to get the result as we excepted.
Measure = CALCULATE(SUM(fabric_swishs[values]),FILTER(fabric_swishs,fabric_swishs[month]="April" && fabric_swishs[Year]=2018 && fabric_swishs[Type of caller]="email"))
For more details, please check the pbix as attached. If it doesn't meet your requirement, kindly share your sample data to me.
https://www.dropbox.com/s/socoo6aggzse5e7/Fiscal%20Year%20Calculated%20Columns.pbix?dl=0
Regards,
Frank
Thanks, I used the following but results in an error :
SyntaxEditor Code Snippet
Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" & fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]="Family"))
Table name
fabric_swishs
Pick list that has multiple values
fabric_typeofcaller
Month I am filtering on
fabric_dateofcall
Error
Hi @marcus505,
Could you please share your pbix to me? You can upload your pbix to dorpbox and paste the link here directly.
Regards,
Frank
Hi @marcus505,
The highlighted part in your formula, there is no "Family" in the column. So the error will occur. If we delete the part and update the formula to a new one, that will work well.
Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]="Family"))
Measure2 = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018))
Please check the pbix as attached.
https://www.dropbox.com/s/hbu25f4vct5lbky/FiscalData.pbix?dl=0
Regards,
Frank
Hi Frank,
Thanks a lot.
Type of caller is a pick list which has multiple values.
Family
Self
Teacher
The only filter missing is type of caller. When i applied the filter, i got an error shown in the previous post.
Please let me know if you have any questions.
Thanks,
Hi Frank,
I was applying the filter by label "Family". As you can see below (this is Dynamics CRM)
So in that case i would need to filter TypeOfCaller by the fieldvalue and sum them i take it rather than Label?
Thanks
Hi @marcus505,
Yes, here you should update your formula like this.
Measure = CALCULATE(SUM(fabric_swishs[fabric_typeofcaller]),FILTER(fabric_swishs,fabric_swishs[fabric_dateofcall].[Month]="August" && fabric_swishs[fabric_dateofcall].[Year]=2018 && fabric_swishs[fabric_typeofcaller]= 555510002))
Regards,
Frank
Thanks a lot Frank, Greatly appreciate your help.
I will take it from here.
Thanks,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.