Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ramshoney1225
Helper V
Helper V

Urgent : Need to calculate a Measure SUM based on Group by Few columns in a single Table

‎04-23-2020 02:16 AM

Hi 

 

I have a table where i need to calucate a measure and that meausre i'm using in a CARD.

I need to create a Measure in a s way that it should SUM(Utiliz_Time_in_Mins) Group by (Month, UniqueID)

 

Below is the data set i'm using

 

divisionregiondistriCTAPMonthUniquIDUtiliz_Time_in_Mins
ProjeCTAPsPM MCP WestPMW Top End-Katherine10/19/2019 0:00CTAP ID 1861110
ProjeCTAPsPM MCP WestPMW Top End-Katherine11/11/2019 0:00CTAP ID 1789262
ProjeCTAPsPM MCP WestPMW Top End-Katherine11/19/2019 0:00CTAP ID 39284
ProjeCTAPsPM MCP WestPMW Tennant Creek3/6/2020 0:00CTAP ID 1780127
ProjeCTAPsPM MCP WestPMW Spencer Gulf11/20/2019 0:00CTAP ID 1778434
ProjeCTAPsPM MCP WestPMW Spencer Gulf12/9/2019 0:00CTAP ID 1778202
ProjeCTAPsPM MCP WestPMW Spencer Gulf2/11/2020 0:00CTAP ID 1778151
ProjeCTAPsPM MCP WestPMW Spencer Gulf2/26/2020 0:00CTAP ID 17784
ProjeCTAPsPM MCP WestPMW Spencer Gulf3/3/2020 0:00CTAP ID 466771
ProjeCTAPsPM MCP WestPMW South East11/29/2019 0:00CTAP ID 17272
ProjeCTAPsPM MCP WestPMW South East12/9/2019 0:00CTAP ID 17274
ProjeCTAPsPM MCP WestPMW South East1/2/2020 0:00CTAP ID 260596
ProjeCTAPsPM MCP WestPMW South East2/26/2020 0:00CTAP ID 138040
ProjeCTAPsPM MCP WestPMW Pilbara1/1/1980 0:00CTAP ID 83516
ProjeCTAPsPM MCP WestPMW Pilbara12/13/2019 0:00CTAP ID 216722
ProjeCTAPsPM MCP WestPMW Pilbara12/24/2019 0:00CTAP ID 835122
ProjeCTAPsPM MCP WestPMW Pilbara12/31/2019 0:00CTAP ID 83523
ProjeCTAPsPM MCP WestPMW Pilbara1/16/2020 0:00CTAP ID 83517
ProjeCTAPsPM MCP WestPMW Pilbara1/20/2020 0:00CTAP ID 835118
ProjeCTAPsPM MCP WestPMW Pilbara1/24/2020 0:00CTAP ID 83579
ProjeCTAPsPM MCP WestPMW Pilbara1/31/2020 0:00CTAP ID 34949
ProjeCTAPsPM MCP WestPMW Pilbara2/15/2020 0:00CTAP ID 249732
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 224242
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 2242132
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 2242276
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 2242317
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 2242349
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 2242422
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 3045142
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 3045299
ProjeCTAPsPM MCP WestPMW Perth1/1/1980 0:00CTAP ID 3045336

 

But here i'm not using this measure in any table report to do / apply any context filter in rows.

I'm just using that measure in a CARD Vizualizaiton whre i need to get the SUM(Utiliz_Time_in_Mins) GROUP BY ( MONTH, UNIQUID)  where this measure will apply filters from (division, Region, District ) only 

 

OR 

SUM(Utiliz_Time_in_Mins) GROUP BY ( UNIQUID ,MONTH)

 

seems which results the same right ?, i if have this measure created i need to create the same for sum(x) based on 7 group by coulmns in another table 😞 

 

 

Need urgency .

 

Thanks,
rams

 

Labels:
Message 1 of 9
547 Views
0
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User
In response to ramshoney1225

‎04-23-2020 03:12 AM

the sum at the group level will not make any diff. Not sure of objective _1 or _2 are the column name for the new Aggregated columns in summarize, it can be any name


MEASURE2 = sumx(summarize(Table,Table[Divison] ,

Table[Region] ,

Table[District] ,

Table[Caterogy] ,

Table[MONTH],"_4",SUM(Table[Utiliz_Time_in_Mins]) ),[_4])


MEASURE1 = sumx(summarize(Table,Table[UNIQUID] ,Table[MONTH],"_1",SUM(Table[Utiliz_Time_in_Mins]) ),[_1])

 

I think Measure 3 should be like this
AverageX(summarize(Table,Table[MONTH],"_1",SUM(Table[Utiliz_Time_in_Mins]),[_2],SUM(Table[Utiliz_Time_in_Mins]) ),divide([_2],[_1]))



View solution in original post

Message 4 of 9
525 Views
0
8 REPLIES 8
amitchandak
Super User
Super User

‎04-23-2020 02:37 AM

What is the advantage of GROUP BY ( UNIQUID ,MONTH) when you want to display on card. If you want data to grouped use table or matrix visual

 

You can have measure like this


sumx(summarize(Table,Table[UNIQUID] ,Table[MONTH,"_1",SUM(Table[Utiliz_Time_in_Mins]) ),[_1])



Message 2 of 9
541 Views
0
ramshoney1225
Helper V
Helper V
In response to amitchandak

‎04-23-2020 03:03 AM

Hi Amitchandak, 


Thanks for the very promtp response, ya actually we are doing some pilot project which they were coming up with single table with almost all data but need to calcuate the Sum(x_measure) based on Group by ( a, b, c ) columns Individually and placing them in Cards. ( where this card will get filtered for d,e,f columns as slicers ) 

 

I didnt understand why "_1" using 😐 

 

Now i need to create another measure in another table that should be having below columns

( Divison,region,district,category,Month, and another few more columns and 1 calc measure i created "Forecast roll Minutes")

 

New measure i need to create is 

 

SUM("Forecast roll Minutes") Based on Group By ( Divison,region,district,category,Month )

 

 

 

So in this case how cani write ? 

MEASURE2 = sumx(summarize(Table,Table[Divison] ,

                         Table[Region] ,

                         Table[District] ,

                         Table[Caterogy] ,

                         Table[MONTH,"_4",SUM(Table[Utiliz_Time_in_Mins]) ),[_4])     ??

 

Even if the order of the colums are present in jumbling order also the above and the given mention will work 🙂 

MEASURE1 = sumx(summarize(Table,Table[UNIQUID] ,Table[MONTH,"_1",SUM(Table[Utiliz_Time_in_Mins]) ),[_1])

 

Now i need to create MEASURE3 = DIVIDE( MEASURE1, MEASURE2, 0 )

 

 

Once again thanks for the real help 🙂 

Message 3 of 9
528 Views
0
ramshoney1225
Helper V
Helper V
In response to ramshoney1225

‎04-23-2020 03:13 AM

Got it,  Amitchandak,

 

MEASURE1 = sumx(summarize(Table,Table[UNIQUID] ,Table[MONTH,"_1",SUM(Table[Utiliz_Time_in_Mins]) ),[_1])

 

I think we missed closed brace for MONTH above 🙂

MEASURE1 = sumx(

                                summarize(

                                                    Table,

                                                              Table[UNIQUID] ,

                                                              Table[MONTH],

                                                             "Result_Set",SUM(Table[Utiliz_Time_in_Mins])
                                                    ),

                               "Result_Set"

                                )

Message 9 of 9
483 Views
0
amitchandak
Super User
Super User
In response to ramshoney1225

‎04-23-2020 03:12 AM

the sum at the group level will not make any diff. Not sure of objective _1 or _2 are the column name for the new Aggregated columns in summarize, it can be any name


MEASURE2 = sumx(summarize(Table,Table[Divison] ,

Table[Region] ,

Table[District] ,

Table[Caterogy] ,

Table[MONTH],"_4",SUM(Table[Utiliz_Time_in_Mins]) ),[_4])


MEASURE1 = sumx(summarize(Table,Table[UNIQUID] ,Table[MONTH],"_1",SUM(Table[Utiliz_Time_in_Mins]) ),[_1])

 

I think Measure 3 should be like this
AverageX(summarize(Table,Table[MONTH],"_1",SUM(Table[Utiliz_Time_in_Mins]),[_2],SUM(Table[Utiliz_Time_in_Mins]) ),divide([_2],[_1]))



Message 4 of 9
526 Views
0
ramshoney1225
Helper V
Helper V
In response to amitchandak

‎04-23-2020 05:08 AM

HI Amitchandak,

 

I have created the below two measures :

 

FC MinsGroupBY =
SUMX(summarize(Forecast_data,
Forecast_data[Divn] ,
Forecast_data[Regn],
Forecast_data[Dist],
Forecast_data[Category],
Forecast_data[Month],
"Result_set",SUM(Forecast_data[Forecast Worktime] ) ),[Result_set])
 
Duration GroupBY =
AVERAGEX(summarize(Actual_data,
Actual_data[divn] ,
Actual_data[regn],
Actual_data[dist],
Actual_data[category],
Actual_data[due_date],
"Result_set",SUM(Actual_data[Utilization Mins]) ),[Result_set])
 
Demand_gap = 
DIVIDE([FC MinsGroupBY] , [Duration GroupBY]  , 0 )
 
Which im getting Huge% Numbers  😞 , which it show less% values , Unable to use Average 😞 
 
Message 6 of 9
508 Views
0
amitchandak
Super User
Super User
In response to ramshoney1225

‎04-23-2020 05:16 AM

@ramshoney1225 

I am really not sure, why you are doing this. Create common dimension (One ore more ) for Divn, region, Dist category and Month and then this should work

divide(SUM(Forecast_data[Forecast Worktime] ),SUM(Actual_data[Utilization Mins]))

 

If Divn, region, Dist  unique because of Dist  they can be one dimension

 

 

 



Message 7 of 9
504 Views
0
ramshoney1225
Helper V
Helper V
In response to amitchandak

‎04-23-2020 05:30 AM

😐 Actually they were getting some output into individual tables through optimization rules created in Python / Rscript and they were trying to optimize them more, meanwhile what eer the output i'm loading into table and based on there requirment i'm doing 😐 

Just working on a Quick POC demo 😞 , so what ever they miss i'm doing in Power BI.

Message 8 of 9
500 Views
0
ramshoney1225
Helper V
Helper V
In response to amitchandak

‎04-23-2020 03:17 AM

Amitchandak 😎 👍, Thanks for helping me and making me to understand in a very easy manner.

and thanks for helping me in new learning.

 

Thanks,

Rams

Message 5 of 9
522 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All