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: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
724 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]))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

Message 4 of 9
702 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])

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 2 of 9
718 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
705 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
660 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]))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 4 of 9
703 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
685 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

 

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
 Online Event
Register Here
Message 7 of 9
681 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
677 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
699 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All