Issue in defining a measure

[LorenzoC]

Dear All,

i have the following dataset:

Id	Date	S_Cost	D_Cost	Tot_D_Cost
1	01/01/2024	500	1500	3300
1	01/01/2024	500	1800	3300
1	03/01/2024	2000	2500	2500
2	01/02/2024	500	2100	3300
2	02/02/2024	500	2800	3300
2	10/02/2024	2000	4200	2500

Wha I need is define a MEASURE (or at least a calculated column) which splits the value of TOTAL DISTINCT S_Cost with reference to the Id and Date.

Firstly I created two measures: 

DISTINCT_S_Cost = SUMX(

    SUMMARIZE(

        'Tabella 3 (Foglio2)',

        'Tabella 3 (Foglio2)'[Id],

        'Tabella 3 (Foglio2)'[S_Cost]

    ),

    'Tabella 3 (Foglio2)'[S_Cost]

)

and 

DISTINCT_TOTAL_D_Cost = SUMX(

    SUMMARIZE(

        'Tabella 3 (Foglio2)',

        'Tabella 3 (Foglio2)'[Id],

        'Tabella 3 (Foglio2)'[Tot_D_Cost]

    ),

    'Tabella 3 (Foglio2)'[Tot_D_Cost]

)

Then I created the my FINAL MEASURE, which was supposed to give my desired result:

Final_Measure = DIVIDE(SUM('Tabella 3 (Foglio2)'[D_Cost]),[DISTINCT_TOTAL_D_Cost])* [DISTINCT_S_Cost]

However, this does not work.

The following are my wrong result with my final measure and my desired result which i cannot achieve:

Id	Date	S_Cost	D_Cost	Tot_D_Cost	DESIRED RESULT
1	01/01/2024	500	1500	3300	646,5517241
1	01/01/2024	500	1800	3300	775,862069
1	03/01/2024	2000	2500	2500	1077,586207
2	01/02/2024	500	2100	3300	692,3076923
2	02/02/2024	500	2800	3300	923,0769231
2	10/02/2024	2000	4200	2500	1384,615385

Wrong result:

Do you think I can adjust this final measure to reach my desired result?
Thank you a lot

Lorenzo

[rajendraongole1]

Hi @LorenzoC - Creating calculated columns for S_Cost and Tot_D_Cost per Id and Date

Calculated column for Total S cost distinct

Distinct_Total_S_Cost =

CALCULATE(

    SUM('Desired'[S_Cost]),

    ALLEXCEPT('Desired', 'Desired'[Id], 'Desired'[Date])

)

Another calculated column : 

Distinct_Total_D_Cost =

CALCULATE(

    SUM('Desired'[Tot_D_Cost]),

    ALLEXCEPT('Desired', 'Desired'[Id], 'Desired'[Date])

)

create a calculated column to calculate the the distinct totals as below:

Final_Result =

DIVIDE(

    'Desired'[D_Cost],

    'Desired'[Distinct_Total_D_Cost]

) * 'Desired'[Distinct_Total_S_Cost]

In report:

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[LorenzoC]

Dear @rajendraongole1 ,

thank you a lot for your answer. Unfortunately what you achieved is my wrong result.

What I want to achieve through a measure or a calculated column is the following (Desired Result):

Id	Date	S_Cost	D_Cost	Tot_D_Cost	DESIRED RESULT
1	01/01/2024	500	1500	3300	646,5517241
1	01/01/2024	500	1800	3300	775,862069
1	03/01/2024	2000	2500	2500	1077,586207
2	01/02/2024	500	2100	3300	692,3076923
2	02/02/2024	500	2800	3300	923,0769231
2	10/02/2024	2000	4200	2500	1384,615385

[rajendraongole1]

Hi @LorenzoC - Can you help to understand with one record how we are getting desired result 646.55 for 1 record?

Id	Date	S_Cost	D_Cost	Tot_D_Cost	DESIRED RESULT
1	01/01/2024	500	1500	3300	646.5517241

I have applied with formula dividing D_Cost by S_Cost and Tot_D_Cost as multiply.

Desired_Result = DIVIDE([DC] * 100, [SC]) * DIVIDE([TDC], 100)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[LorenzoC]

Dear @rajendraongole1 ,

the formula i used (in excel) for obtaining my desired result is:

(D_Cost / SUM Distinct Tot_D_Cost) * SUM_Distinct S_Cost

which in table is:

Id	Date	S_Cost	D_Cost	Tot_D_Cost	DESIRED RESULT	Formula for DESIRED RESULT
1	01/01/2024	500	1500	3300	646,5517241	(1500/5800)*2500
1	01/01/2024	500	1800	3300	775,862069	(1800/5800)*2500
1	03/01/2024	2000	2500	2500	1077,586207	(2500/5800)*2500
2	01/02/2024	500	2100	3300	692,3076923	(2100/9100)*3000
2	02/02/2024	500	2800	3300	923,0769231	(2800/9100)*3000
2	10/02/2024	2000	4200	2500	1384,615385	(4200/9100)*3000

[Moetazzahran]

Hello @LorenzoC , 

Can you please explain how you calculated your desired result? To have better understanding of the filter context as your explanation is not so clear in the original post. 
Thank you.