Forum Discussion

jmorela1's avatar
jmorela1
Icon for Helper I rankHelper I
7 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
    7 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.

     



Olufemi7's avatar
Olufemi7
Icon for Super User rankSuper User
7 months ago

Hello jmorela1,

 

This is a filter context issue. In both Power BI Desktop and Microsoft Fabric, you should define explicit measures for Cost, Clicks, Conversions, and Completes, then calculate CPC/CPA/Cost per Complete using `DIVIDE`. This ensures slicers (BU, Channel) apply consistently.

Example Measures

Total Cost = SUM(PaidMedia[Cost])
Total Completes = SUM(PaidMedia[FormCompletes])
Total Clicks = SUM(PaidMedia[Clicks])
Total Conversions = SUM(PaidMedia[Conversions])

Cost per Complete = DIVIDE([Total Cost], [Total Completes])
CPC = DIVIDE([Total Cost], [Total Clicks])
CPA = DIVIDE([Total Cost], [Total Conversions])


Key Notes

• Make sure BU and Channel slicers are connected via relationships.
• If BU/Channel are in separate dimension tables, use `TREATAS` to apply slicer filters across tables.
• If slicers still break context, wrap your measure in `CALCULATE` with `ALLSELECTED` to preserve selected filters.


Works in Both Power BI & Fabric

The DAX engine is the same in Power BI Desktop and Fabric semantic models, so these measures behave identically in both environments.

 

Microsoft Documentation

 

• Context in DAX formulas (row vs. filter context)

`https://support.microsoft.com/en-us/office/context-in-dax-formulas-2728fae0-8309-45b6-9d32-1d600440a7ad`

 

• CALCULATE function (DAX)

`https://learn.microsoft.com/en-us/dax/calculate-function-dax`

 

• ALLSELECTED function (DAX)

`https://learn.microsoft.com/en-us/dax/allselected-function-dax`

 

• TREATAS function (DAX)

`https://learn.microsoft.com/en-us/dax/treatas-function-dax`

 

• Semantic models in Microsoft Fabric

`https://learn.microsoft.com/en-us/fabric/data-warehouse/semantic-models

jmorela1's avatar
jmorela1
Icon for Helper I rankHelper I
7 months ago
Cost per Google Form Complete = CALCULATE(DIVIDE([Total Spend Measure],[Total form completes measure]), ALLSELECTED('OEG Consolidated'[Channel],'OEG Consolidated'[Subset BU]))
 
Can you please advise on the above, re your direction to wrap?
  • V-yubandi-msft's avatar
    V-yubandi-msft
    Icon for Community Support rankCommunity Support
    7 months ago

    Hi jmorela1 ,

    The DAX formula you provided is correct, but using DIVIDE inside CALCULATE with ALLSELECTED isn’t necessary for standard slicer functionality.  Channel and Subset BU slicers automatically apply the filter context if relationships are set up properly. If selecting these slicers doesn’t change the value, it’s likely an issue with the data or model, not the DAX structure.

     

    Currently, Channel and Subset BU only have one distinct value each, so selecting them won’t affect the filter context or results, which is expected behavior.

    The suggested formula is

    Cost per Google Form Complete =
DIVIDE([Total Spend Measure], [Total form completes measure])

    ALLSELECTED is mainly used in specific cases, such as when handling totals across selected visuals. Using it in this context won’t address the issue and may obscure data or modeling problems.

     

    If Channel and Subset BU should have multiple values, reviewing the source data and relationships is recommended.

    Hope this helps..

     

    Regards,

    Yugandhar.