Calculating fixed Qutient

[Pfoster]

Hello all,

I am struggeling since one week at a Problem and can not find a sufficiend solution:
I have my Source Table 'Sales Data'. Here, I have per Customer-Product-Scenario a Margin and Sales Volumes. For a complex calculation of Margins splitted by producing company, I have created two different keys:

GlobalMaterial_ST_ID = 

and 

See-Through_Product = 

Now, I want to have for each See-Through_Product the See-Through_Margin_per_MT, which is deffined by:
[FocusCMA_in_RC] / [FocusMT] when See-Through_Product = GlobalMaterial_ST_ID. (That is the only precondition). This Quotient should not be changed by any filter (on Customer, Company or so), so that the Quotient is equal for all the same See-Through_Products, doesn't matter, to whom the original product is sold. 

Therefore, I have created an extra calculated table to have a list of the single See-Through_Products. I put then a 1:N relation between this See-Through_Product column and the GlobalMaterial_ST_ID in the 'Sales Data', to calculate with [FocusMT] and [FocusCMA_in_RC]... but what to say: The result is not sufficient, because the calculated quotient is based on the filtering of the report. 
Now my thought is: Do the calculation of the quotient in the calculated See-Through_Product Table and bring the ST_CMA_inMT with a related() form back into the 'Sales Data' table. but now, I am struggeling getting the right [FocusMT] and [FocusCMA_in_RC] into the See-Through_Product Table... Any suggestions? 

(hope, I descripted my problem understandable). 

[Pfoster · Accepted solution]

I found the solution:
1. I creaded the calculated Table:

SeeThroughProducts = 
FILTER(
    SUMMARIZE(
        'Sales Data',
        'Sales Data'[See-Through_Product]
    ),
    NOT ISBLANK('Sales Data'[See-Through_Product])
)

2. I added a Measure in that table for Sales Volumes (in 3. also for Margin):

SalesReported_FocusScenarioYear = 
VAR CurrentMaterialID = SELECTEDVALUE(SeeThroughProducts[See-Through_Product])
RETURN
CALCULATE(
    SUM('Sales Data'[Sales Reported]),
    FILTER(
        ALL('Sales Data'),
        'Sales Data'[GlobalMaterial_ST_ID] = CurrentMaterialID
    ),
    'Sales Data'[Scenario]= SELECTEDVALUE(FocusScenario[Focus Scenario]),
    'Sales Data'[Year]=SELECTEDVALUE(GemeinsameJahre[Year])
)

3. like 2. only with Margin
4. I created a Measure for the quotient:

ST_CMA_per_MT = DIVIDE([CMA_FocusScenarioYear],[SalesReported_FocusScenarioYear],0)

5. In my Source Table, I added following Measure:

ST_CMA_per_MT_for_SalesData = 
VAR CurrentSTProduct = SELECTEDVALUE('Sales Data'[See-Through_Product])
RETURN
CALCULATE(
    [ST_CMA_per_MT],
    FILTER(
        ALL(SeeThroughProducts),
        SeeThroughProducts[See-Through_Product] = CurrentSTProduct
    ),
    KEEPFILTERS(VALUES('Sales Data'[Year])),
    KEEPFILTERS(VALUES(FocusScenario[Focus Scenario])
))

Now, it works, and I can go on with trying the next one...

[pankajnamekar25]

Hello @Pfoster 

Create a calculated table to store the unfiltered ratio per See-Through Product, then bring the ratio back into your Sales Data table via a RELATED() lookup.

 Step-by-Step Implementation

Create a calculated table of See-Through Products

dax

CopyEdit

SeeThroughProductTable =

SUMMARIZE(

    'Sales Data',

    'Sales Data'[See-Through_Product]

)

This gives you a distinct list of See-Through Products.

Add a calculated column in that table to compute the unfiltered ratio

SeeThrough_Margin_per_MT =

VAR CurrentProduct = 'SeeThroughProductTable'[See-Through_Product]

RETURN

    DIVIDE(

        CALCULATE(

            SUM('Sales Data'[FocusCMA_in_RC]),

            REMOVEFILTERS('Sales Data'),

            'Sales Data'[GlobalMaterial_ST_ID] = CurrentProduct

        ),

        CALCULATE(

            SUM('Sales Data'[FocusMT]),

            REMOVEFILTERS('Sales Data'),

            'Sales Data'[GlobalMaterial_ST_ID] = CurrentProduct

        )

    )

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Pfoster]

Unfortunately, this procedure does not work.
In Excel, it would actually be a sum-if function:
If 'Sales Data'[GlobalMaterial_ST_ID] = 'SeeThroughProductTable'[SeeThroughProduct], then add [FocusMT]. And of course the same in another column for the margin.
If I had that, I could then create a measure in ‘Sales Data’ that calculates the quotient for me.