Measure is disrupted by display context

DAX DAX Calculations
1

image

image

Product _ Gross Margin - Issue.pbix (1.2 MB)

Budget Data _offline.xlsx (14.6 KB)
Fact Table.xlsx (5.6 MB)
Forecast Data _offline.xlsx (15.2 KB)

2

Hello @mdalton2100,

Thank You for posting your query onto the Forum.

To achieve the results based on the criteria that you’ve specified. Below is the measure alongwith the screenshot of the final results provided for the reference -

Flux % - Cost of Services (Actuals) - Harsh = 


----- Cost of Services Calculation -----


VAR Actual_CQ_Revenue = 
CALCULATE( 
    CALCULATE( [Actuals (+/-)] , 
        FILTER( 'Gross Margin transactions' , 
            'Gross Margin transactions'[Gross Margin element] = "Products Revenue" ) ) , 
    'Mapping for Flux analysis only'[Gross Margin group_Level 0] = "Revenue" )

VAR Actual_CQ_CoSvces = 
CALCULATE( 
    CALCULATE( [Actuals (Flux) CQ] , 
        FILTER( 'Mapping for Flux analysis only' , 
            'Mapping for Flux analysis only'[Flux Category] = "Cost of Services" ) ) , 
    'Mapping for Flux analysis only'[Flux Category] = "Cost of Services" )

VAR Actuals_PQ_Revenue = 
CALCULATE(
    CALCULATE( 
        CALCULATE( [Actuals (+/-)] , 
            FILTER( 'Gross Margin transactions' , 
                'Gross Margin transactions'[Gross Margin element] = "Products Revenue" ) ) , 
        'Mapping for Flux analysis only'[Gross Margin group_Level 0] = "Revenue" ) ,
    DATEADD( Dates[Date] , -1 , QUARTER ) )

VAR Actuals_PQ_CoSvces = 
CALCULATE(
    CALCULATE( 
        CALCULATE( [Actuals (Flux) CQ] , 
            FILTER( 'Mapping for Flux analysis only' , 
                'Mapping for Flux analysis only'[Flux Category] = "Cost of Services" ) ) , 
        'Mapping for Flux analysis only'[Flux Category] = "Cost of Services" ) , 
    DATEADD( Dates[Date] , -1 , QUARTER ) )

VAR Actual_CQ_CoSvces_Ratio = 
DIVIDE( Actual_CQ_CoSvces , Actual_CQ_Revenue , 0 )

VAR Actuals_PQ_CoSvces_Ratio = 
DIVIDE( Actuals_PQ_CoSvces , Actuals_PQ_Revenue , 0 )


----- Mfg Spend Calculation -----


VAR Actual_CQ_Mfg_Spends = 
CALCULATE( [Actuals (Flux) CQ] , 
    FILTER( 'Mapping for Flux analysis only' , 
        'Mapping for Flux analysis only'[Flux Category] = "Mfg Spend" ) )

VAR Actuals_PQ_Mfg_Spends = 
CALCULATE(
    CALCULATE( [Actuals (Flux) CQ] , 
        FILTER( 'Mapping for Flux analysis only' , 
        'Mapping for Flux analysis only'[Flux Category] = "Mfg Spend" ) ) , 
    DATEADD( Dates[Date] , -1 , QUARTER ) )

VAR Actual_CQ_Mfg_Spend_Ratio = 
DIVIDE( Actual_CQ_Mfg_Spends , Actual_CQ_Revenue , 0 )

VAR Actuals_PQ_Mfg_Spend_Ratio = 
DIVIDE( Actuals_PQ_Mfg_Spends , Actuals_PQ_Revenue , 0 )


----- Results -----


RETURN
SWITCH( TRUE() , 
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Cost of Services" ,
        ABS( Actual_CQ_CoSvces_Ratio - Actuals_PQ_CoSvces_Ratio ) ,
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Mfg Spend" ,
        ABS( Actual_CQ_Mfg_Spend_Ratio - Actuals_PQ_Mfg_Spend_Ratio ) ,
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "D C" ,
        BLANK() ,
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Material CoGs" ,
        BLANK() , 
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Mfg OH CoGS" ,
        BLANK() , 
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Products Revenue" ,
        BLANK() , 
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "GAAP Adjustment" ,
        BLANK() ,
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Revaluation" ,
        BLANK() , 
    SELECTEDVALUE( 'Mapping for Flux analysis only'[Flux Category] ) = "Services Revenue" ,
        BLANK() ,
        ABS((  Actual_CQ_CoSvces_Ratio - Actuals_PQ_CoSvces_Ratio ) + ( Actual_CQ_Mfg_Spend_Ratio - Actuals_PQ_Mfg_Spend_Ratio ) ) )

Final Results
Final Results1393Ă—797 111 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Product _ Gross Margin - Issue - Harsh.pbix (1.2 MB)

1 Like
3

Thank you so much for that detailed answer @Harsh

We hope this helped you @mdalton2100 :slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

4

Hi @mdalton2100, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

5

Sorry for the delay reply. Yes, this solution was effective! Thanks again.