Forum Discussion
Divide With a Sum and Filter on same column
I am new to PowerBI and have searched the forum, which has been very helpful, but I'm stuck on what I think is a very simple task, but the results are not as expected.
I have a Filtered, Grouped table of phone call records with the following columns:
- User Name
- Date
- Direction (True/False)
- Count (count of rows based on the grouping of the above three fields)
I want to come up with a percentage of incoming calls (direction = false) to outgoing calls (direction = true) and have the following calculation:
CallBack = DIVIDE( CALCULATE(sum('Raw Calls'[Count]), 'Raw Calls'[DirectionCode]=FALSE()) , CALCULATE(sum('Raw Calls'[Count]), 'Raw Calls'[DirectionCode]=TRUE()) )
It does not give an error, but when I put it into a "Card" visulization, the result is "Blank"
So I thought I could create two variables: Incoming and Outgoing, then just divide those. The formulas for those were:
Incoming = CALCULATE(sum('Raw Calls'[Count]), 'Raw Calls'[DirectionCode]=FALSE())
Outgoing = CALCULATE(sum('Raw Calls'[Count]), 'Raw Calls'[DirectionCode]=TRUE())
The first one creates just fine, but when I create the second on I get a circular reference error.
What am I doing wrong?
-Frank
fenneran That error indicates you are creating COLUMNS! Convert them to MEASURES!
Try this MEASURE!
It basically just adds zero to the one you've already created but adding zero is by far the easiest way to handle blank in a card!
CallBack = DIVIDE ( CALCULATE ( SUM ( 'Raw Calls'[Count] ), 'Raw Calls'[DirectionCode] = FALSE () ), CALCULATE ( SUM ( 'Raw Calls'[Count] ), 'Raw Calls'[DirectionCode] = TRUE () ), 0 ) + 0I can't simulate your grouping and filtering but here is what I did:
Using this Enter Data query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi9KTVfSUTI00AciIwNDMyDHLTGnOBVIGynF6mBXEVJUClJgDFaQlJmTg8sI3CqgRpii22GIbgRuFVAjDA3QLcEww9AQpxKYIUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User Name" = _t, Date = _t, Direction = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Name", type text}, {"Date", type date}, {"Direction", type logical}, {"Count", Int64.Type}}) in #"Changed Type"I created the following measure that returned the correct result:
CallBack = VAR Incoming = CALCULATE(SUM([Count]),ALL('Raw Calls'),'Raw Calls'[Direction]=FALSE()) VAR Outgoing = CALCULATE(SUM([Count]),ALL('Raw Calls'),'Raw Calls'[Direction]=TRUE()) RETURN Incoming/Outgoing
3 Replies
fenneran That error indicates you are creating COLUMNS! Convert them to MEASURES!
Try this MEASURE!
It basically just adds zero to the one you've already created but adding zero is by far the easiest way to handle blank in a card!
CallBack = DIVIDE ( CALCULATE ( SUM ( 'Raw Calls'[Count] ), 'Raw Calls'[DirectionCode] = FALSE () ), CALCULATE ( SUM ( 'Raw Calls'[Count] ), 'Raw Calls'[DirectionCode] = TRUE () ), 0 ) + 0I can't simulate your grouping and filtering but here is what I did:
Using this Enter Data query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSi9KTVfSUTI00AciIwNDMyDHLTGnOBVIGynF6mBXEVJUClJgDFaQlJmTg8sI3CqgRpii22GIbgRuFVAjDA3QLcEww9AQpxKYIUqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"User Name" = _t, Date = _t, Direction = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"User Name", type text}, {"Date", type date}, {"Direction", type logical}, {"Count", Int64.Type}}) in #"Changed Type"I created the following measure that returned the correct result:
CallBack = VAR Incoming = CALCULATE(SUM([Count]),ALL('Raw Calls'),'Raw Calls'[Direction]=FALSE()) VAR Outgoing = CALCULATE(SUM([Count]),ALL('Raw Calls'),'Raw Calls'[Direction]=TRUE()) RETURN Incoming/OutgoingThank you both, that is exactly what I needed. I know it was something simple!
Best,
Frank
