Forum Discussion
Calculating fixed Qutient
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).
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...
3 Replies
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
If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
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.
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...
