Forum Discussion

maruthinarra's avatar
maruthinarra
Frequent Visitor
8 years ago
Solved

Two step aggregation along with Group by , Sum and Max

Hello All,   am trying to calculate measure on Power BI dataset but am not able to come up with any clue how to write two step aggregation on same column.   Following is my query    SELECT MAX(...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi maruthinarra,

     

    Try this formula please.

    Measure =
VAR midTable =
    SUMMARIZE (
        'FACT_TABLE',
        FACT_TABLE[customer_number],
        "SWTD", SUM ( FACT_TABLE[invoice_amt] ) / SUM ( FACT_TABLE[invoiced_qty] )
    )
RETURN
    MAXX ( midTable, [SWTD] ) / MINX ( midTable, [SWTD] )

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi maruthinarra,

 

Try this formula please.

Measure =
VAR midTable =
    SUMMARIZE (
        'FACT_TABLE',
        FACT_TABLE[customer_number],
        "SWTD", SUM ( FACT_TABLE[invoice_amt] ) / SUM ( FACT_TABLE[invoiced_qty] )
    )
RETURN
    MAXX ( midTable, [SWTD] ) / MINX ( midTable, [SWTD] )

Best Regards,

Dale

maruthinarra's avatar
maruthinarra
Frequent Visitor
8 years ago

Thanks a lot Dale, 

 

Its working azaingly :) Great Help