group by for aggregations

[Anonymous]

Hi,

I have to create multiple measures using group by. I tried like below but they are creating separate tables, i need these measures in the same table only. 

Please suggest how to acheive this.

1. Total =

GROUPBY (

FILTER ( 'Invo', 'Invo'[St_ID] = 580 ),

'Invo'[Step_ID],

'Invo'[Aket_ID],

"Total Units", COUNTX ( CURRENTGROUP (), 'Invo'[Aket_ID] )

)

2. 

Avg Outstanding =

GROUPBY (

FILTER (

'Invo',

'Invo'[Act] <> BLANK ()

&& 'Invo'[Act] <> 0

&& 'Invo'[St_ID] <> 500

),

'Invo'[Stor_ID],

'Invo'[Aket_ID],

"Avg Outstanding", AVERAGEX ( CURRENTGROUP (), 'Invo'[Outstanding] )

)

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

The GROUPBY function will return a table by default. If you want to use the grouped table in a measure, simply use the VAR function like this:

Measure =
VAR Total =
    GROUPBY (
        FILTER ( 'Invo', 'Invo'[St_ID] = 580 ),
        'Invo'[Step_ID],
        'Invo'[Aket_ID],
        "Total Units", COUNTX ( CURRENTGROUP (), 'Invo'[Aket_ID] )
    )
RETURN
    SUMX ( Total, [Total Units] )

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft ,

Thanks for the reply.

Can you please also tell me what to write for second measure?

Thanks

[v-yanjiang-msft]

Hi @Anonymous ,

I only want to show you how to use a table in a measure in my sample formula. You haven't said what you want to chieve, if you need further help, would you like to provide more information about your sample and expected result?

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Sure @v-yanjiang-msft , The aggregations are not feasible at source (SQL) due to complexity and performance issues. I have to do approx 40 different aggregations, most of them have to use group by, within the Power BI for this report. The majority of these aggregations are Count and Average but there are few divide and substractions.

There are few measures which need to be calculate by referring the measures itself like:

Total % = Total / SUM(Total) * 100

When i tried to Calculate Total % by using the Total measure above given by you, it doesn't work as it needs column for SUM. So, all such meausres which referes a measure itself creating more issues for me to calculate.

I hope you can get some idea from this explanation. Please share your thought or some solution for the problem statement. 

Thanks

Thanks