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'm trying to write a calculate function comvbined with divide that should output a percentage.
Background: I have a measure which is grouped by a field called "Channel" which has 8 categories within it.
What I want is to get the proportion of the measure where the Channel used is the combined total of 3 specified channels but i'm getting an error (copied below the code).
% actual Delivery = CALCULATE( DIVIDE( [Volumes (by Month)], ([Volumes (by Month)] ('1819 transposed'[Channel ] && "F2F" && "Online" && "Tel")) ))
See code above - think there's something obvious I'm missing but any help greatly appreciated:
Error Message:
MdxScript(Model) (98, 10) Calculation error in measure '1819 transposed'[% actual Delivery]: Cannot convert value 'Pending' of type Text to type True/False.
Solved! Go to Solution.
Hi @david_MAS ,
Please refer to below measures:
Sum certain channel = CALCULATE ( SUM ( '1819 transposed'[Volume] ), FILTER ( ALLSELECTED ( '1819 transposed' ), '1819 transposed'[Channel] IN { "F2f", "Tel", "Online" } ) ) Percentage = DIVIDE ( [Sum certain channel], CALCULATE ( SUM ( '1819 transposed'[Volume] ), ALL ( '1819 transposed' ) ) )
Best regards,
Yuliana Gu
Hi @david_MAS ,
Please provide sample data and desired output.
How to Get Your Question Answered Quickly
Regards,
Yuliana Gu
Hi,
Okay, so sample data below - essentialy what I want is to get the sum of the first 3 channels below, but as a percentage of the entire value of the column "volume"
Channel | Volume |
F2f | 10 |
Online | 2 |
Tel | 4 |
No shows | 6 |
Pending | 8 |
Referred online | 9 |
Reffered tel | 1 |
Self help | 0 |
Hope that makes sense and thank you for the reply.
Hi @david_MAS ,
Please refer to below measures:
Sum certain channel = CALCULATE ( SUM ( '1819 transposed'[Volume] ), FILTER ( ALLSELECTED ( '1819 transposed' ), '1819 transposed'[Channel] IN { "F2f", "Tel", "Online" } ) ) Percentage = DIVIDE ( [Sum certain channel], CALCULATE ( SUM ( '1819 transposed'[Volume] ), ALL ( '1819 transposed' ) ) )
Best regards,
Yuliana Gu
Hi Yuliana,
Thanks for this, that's looking realy good!
As an extension to this, say I wanted to show this percentage month-by-month (not cumulative) based on the UK-standard financial year, would this involve anothr filter within this measure or is it more striagtforward than that?
Thanks
Hi @david_MAS ,
With current sample data, I cannot see any relative date field to support Month-by-Month calculation. Since the original question has been worked out, would you please kindly mark the helpful reply as an answer? For further requirement, I would suggest you post a new thread on forum with more detailed sample data together with desired output to make the scenario more clear, so that more community members can view it and provide advice.
Best regards,
Yuliana Gu
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.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |