Forum Discussion

jmorela1's avatar
jmorela1
Icon for Helper I rankHelper I
9 months ago
Solved

Calculation not working when slicers applied

I am trying to calculate a cost per Google form complete, but it does not calculate properly when BU or channel is selected. I have tried several different calculations/sums/measure, and cannot get t...
  • MFelix's avatar
    MFelix
    9 months ago

    Hi jmorela1 ,

     

    You must add a new measure that is the cost per google for or whatever other name you want to give it and di the division of both the measures you refere:

     

    Has you can see on my image the measure for the division between both of this values gives the correct value:

     

    In the file I have shared search for the measure Cost per google form and use it on the matrix you should get the correct value.

     



Manasa_Savanur's avatar
Manasa_Savanur
Icon for Helper II rankHelper II
9 months ago

Hi jmorela1 

 

Looks like your organization's policy is not letting you share the pbix file outside, or there is some sharepoint issue. (Paid Media.pbix)

Here are a few fixes you can try - 

Ensure there’s a proper relationship between the BU table/column and the table/column used for your CPC and CPA.
 

  • If you are using a DAX measure to calculate, you can apply the filter explicitly. For example:
CPC = CALCULATE(
    SUM(Clicks) / SUM(Cost),
    ALLSELECTED(BU[BU])
)

 

  • Make sure you aggregate correctly - Using an aggregator inside a CALCULATE incorrectly, for example, wrapping SUMX or AVERAGEX over a table without including BU in the row context, can ignore the slicer.

 

  • Alternatively, instead of a complicated measure, you can consider adding a custom column in the Power Query editor for your CPC and CPA calculation. And then build a relationship with the BU table in Model view.
    I would strongly suggest this approach.

 

If this helps, please mark it as a solution so others can benefit too.