Forum Discussion

Amar-Agnihotri's avatar
Amar-Agnihotri
Resolver I
4 years ago
Solved

How to use measure inside Summarize Function

I want to use a measure inside this summarize function -

Table F1 = SUMMARIZE (
SELECTCOLUMNS (
'Opportunity Snapshot',
"Source", 'Opportunity Snapshot'[Start of Quarter - Copy],
"Target", 'Opportunity Snapshot'[Flow one Result],
"Opportunity ID",'Opportunity Snapshot'[Opportunity_ID_18__c],
"Quarter",'Opportunity Snapshot'[Quarter]
),
[Source],
[Target],
[Opportunity ID],
[Quarter],
"Total", SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] )

)

Rather than using SUM function i want to use this measure -

Measure -
Last ACV =
VAR _id = MAX ('Opportunity Snapshot'[Opportunity_ID_18__c])
VAR _date = CALCULATE( MAX('Opportunity Snapshot'[Close_Date__c]), ALLSELECTED ('Opportunity Snapshot'), 'Opportunity Snapshot'[Opportunity_ID_18__c] = _id)
RETURN
CALCULATE (SELECTEDVALUE( 'Opportunity Snapshot'[Incremental ACV (USD)] ), VALUES ('Opportunity Snapshot'[Opportunity_ID_18__c]),'Opportunity Snapshot'[Opportunity_ID_18__c] = _id,'Opportunity Snapshot'[Close_Date__c] = _date )


I am unable to use measure because what i believe SUMMARIZE function doesn't allow you to do that. Can anybody help me out regarding that how can i achieve this

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, 

    As far as I’m concerned, you can use the measures you created before in the newly created Summarize function in the <expression> parameter after the parameter <groupBy_columnName> and <name>, you can use the measure in your DAX formula like this:

    Table F1 = SUMMARIZE (
SELECTCOLUMNS (
'Opportunity Snapshot',
"Source", 'Opportunity Snapshot'[Start of Quarter - Copy],
"Target", 'Opportunity Snapshot'[Flow one Result],
"Opportunity ID",'Opportunity Snapshot'[Opportunity_ID_18__c],
"Quarter",'Opportunity Snapshot'[Quarter]
),
[Source],
[Target],
[Opportunity ID],
[Quarter],
"Total", [Last ACV] )
)

    You can change the measure formula based on your needs.

     

    For more info, please refer to this link:

    https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-use-measure-inside-Summarize-Function/td-p/2147727

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    4 years ago

    Hi, 

    As far as I’m concerned, you can use the measures you created before in the newly created Summarize function in the <expression> parameter after the parameter <groupBy_columnName> and <name>, you can use the measure in your DAX formula like this:

    Table F1 = SUMMARIZE (
SELECTCOLUMNS (
'Opportunity Snapshot',
"Source", 'Opportunity Snapshot'[Start of Quarter - Copy],
"Target", 'Opportunity Snapshot'[Flow one Result],
"Opportunity ID",'Opportunity Snapshot'[Opportunity_ID_18__c],
"Quarter",'Opportunity Snapshot'[Quarter]
),
[Source],
[Target],
[Opportunity ID],
[Quarter],
"Total", [Last ACV] )
)

    You can change the measure formula based on your needs.

     

    For more info, please refer to this link:

    https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-use-measure-inside-Summarize-Function/td-p/2147727

     

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.