Formula Help

[vincenardo]

I have a table in PBI desktop that I have grouped the data in a matrix report like this -

The Rows being used in the table are;

As you can see in the above matrix, the first row is blank for the 'campaign type'.

When I have the matrix collapsed, I successfully get the 'CPL' value which is

DIVIDE([Total Spend], SUM('R105'[Raw]))

My issue is that I need to calculate this value ($126 in the above example) and then mutiply it times the Raw for each ProjectName/Campaign type so when I expand out the matrix, I can see the 'CPL' for each Campaign Type.

Any suggestions on how this formula would be created?

Thanks in advance!!

[AllisonKennedy]

@vincenardo  You can use the ISINSCOPE function for this, for example:

CPL all = IF( ISINSCOPE ( Table[CampaignType], [CPL] * [Raw], [CPL] )

Trouble is, your CPL is showing as $0 for the CampaignType level, so you'll need to edit it further:

CPL all = IF( ISINSCOPE ( Table[CampaignType],
CALCULATE ( [CPL], ALL(Table[CampaignType]) ) * [Raw], [CPL] )

[lbendlin]

What is your expected outcome?  the value for "Raw" in that row is 0 ?

[vincenardo]

I would like to use the value for 'CPL' based on the rolled up total of the 'Project Name' (CPL = $126) then use that result for the 'campaign types' under the 'project name' and multiply the 'RAW' x the 'CPL' for each;

Internet - $126 (CPL) x 472 (RAW) = $59,472.00

Magazine - $126 (CPL) x 2025 (RAW) = $255,150.00

Showroom - Anaheim - $126 (CPL) x 3 = $378.00

Thanks!