I want to do calculations on values in the same column

[shaik51]

HI,this is the existing dax for the below table:

SUMX('table1', DIVIDE('table1'[WTD] * [VOL], SUM('table1'[VOL])))

ID	WTD	VOL
1	67	33
2	55	44
3	66	55
4	78	66

how can i acheive the same result or write same dax as above for the below new table2 without unpivoting the table and also how can i find MAX(WTD).

ID	PF	values
1	WTD	67
2	WTD	55
3	WTD	66
4	WTD	78
1	VOL	33
2	VOL	44
3	VOL	55
4	VOL	66

 Any Help would be appreciated,Thanks.

 @amitchandak @tamerj1 @FreemanZ @johnt75 @wdx223_Daniel @Greg_Deckler

[wdx223_Daniel]

just for the data you provided,

Measure=SUMX(VALUES('table1'[ID]),DIVIDE(CALCULATE(PRODUCTX('table1','table1'[values])),CALCULATE(SUM('table1'[values]),ALLSELECTED('table1'[ID]))))

[shaik51]

HI @wdx223_Daniel ,there are other values also in the PF column like VAL,UNITS etc,,but I need calulation on VOL and WTD out of all values in PF column. 

[wdx223_Daniel]

if each ID always has one WTD and one VOL then try this

Measure=SUMX(VALUES('table1'[ID]),DIVIDE(CALCULATE(PRODUCTX(FILTER('table1','table1'[PF] IN {"WTD","VOL"}),'table1'[values])),CALCULATE(SUM('table1'[values]),ALLSELECTED('table1'[ID]))))

[tamerj1]

Hi @shaik51 

how do we know which WTD row match with which VAL row? Do we have to match a date or index column? 

[shaik51]

Hi,there is an ID column

thanks

[tamerj1]

@shaik51 

Please try

Result =
SUMX (
GENERATE (
VALUES ( 'Table'[ID] ),
VAR T =
CALCULATETABLE ( 'Table' )
VAR WTD =
MAXX ( FILTER ( T, 'Table'[PF] = "WTD" ), 'Table'[Value] )
VAR VOL =
MAXX ( FILTER ( T, 'Table'[PF] = "VOL" ), 'Table'[Value] )
RETURN
ROW ( "WTD", WTD, "VOL", VOL )
),
[WTD] * [VOL]
)

[shaik51]

showing error as The syntax for ',' is incorrect. (DAX(SUMX (GENERATE (VALUES ( 'VM MarketShare'[VM Product Key] ),VAR T =CALCULATETABLE ( 'VM MarketShare' )VAR WSD =MAXX ( FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "WTD" ), 'VM MarketShare'[VALUE] )VAR VOL =MAXX ( FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "VOLUME" ), 'VM MarketShare'[VALUE] )RETURNROW ( "WTD", WTD, "VOLUME", VOL )),[WSD] * [VOL] ))).

[tamerj1]

@shaik51 

Result =
SUMX (
GENERATE (
VALUES ( 'VM MarketShare'[VM Product Key] ),
VAR T =
CALCULATETABLE ( 'VM MarketShare' )
VAR WSD =
MAXX (
FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "WTD" ),
'VM MarketShare'[VALUE]
)
VAR VOL =
MAXX (
FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "VOLUME" ),
'VM MarketShare'[VALUE]
)
RETURN
ROW ( "WTD", WTD, "VOLUME", VOL )
),
[WSD] * [VOL]
)

[shaik51]

HI @tamerj1 ,Thanks for the quick reply and solution,the measure you have sent is working but it is showing different result values in the visual when compared to the old measure,can you help me on this.

[tamerj1]

@shaik51 
Yes you are right

I missed the DIVIDE part. Yet I still have some doubts regarding which result are you expectiong at the grand total level. Please refer to the attached sample file and let me know.

Result = 
SUMX (
    GENERATE (
        VALUES ( 'VM MarketShare'[VM Product Key] ),
        VAR T =
            CALCULATETABLE ( 'VM MarketShare' )
        VAR _WTD =
            MAXX (
                FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "WTD" ),
                'VM MarketShare'[VALUE]
            )
        VAR VOL =
            MAXX (
                FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "VOLUME" ),
                'VM MarketShare'[VALUE]
            )
        RETURN
            ROW ( "WTD", _WTD, "VOLUME", VOL )
    ),
    DIVIDE ( [WTD] * [VOLUME], [VOLUME] )
)

Result 2 = 
AVERAGEX (
    GENERATE (
        VALUES ( 'VM MarketShare'[VM Product Key] ),
        VAR T =
            CALCULATETABLE ( 'VM MarketShare' )
        VAR _WTD =
            MAXX (
                FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "WTD" ),
                'VM MarketShare'[VALUE]
            )
        VAR VOL =
            MAXX (
                FILTER ( T, 'VM MarketShare'[FACT_ALIAS] = "VOLUME" ),
                'VM MarketShare'[VALUE]
            )
        RETURN
            ROW ( "WTD", _WTD, "VOLUME", VOL )
    ),
    DIVIDE ( [WTD] * [VOLUME], [VOLUME] )
)

[shaik51]

Hi, the above measure is giving wrong values,,like it is showing sum of vaues in the visual.

[shaik51]

HI,there is another ID column with Unique ids,,i just posted a sample of original data,,we can take that column as [ID] for example

ID	PF	values
1	WTD	4
2	WTD	6
3	WTD	5
4	WTD	6
1	VOL	7
2	VOL	8
3	VOL	2
4	VOL	6

thanks.

[FreemanZ]

hi @shaik51 

can you paste your expected result based on table2?

[shaik51]

Hi, I want to achieve the same result of the first dax expression written for table1 for table2,,I want to write a measure that multiplies each WTD value with each VAL value from [value] column and divide it with total sum of VAL from [value] column from the table 2.  and also a measure to find MAX of WTD from table2.

Thanks.