Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
division | region | distriCTAP | Month | UniquID | Utiliz_Time_in_Mins |
ProjeCTAPs | PM MCP West | PMW Top End-Katherine | 10/19/2019 0:00 | CTAP ID 1861 | 110 |
ProjeCTAPs | PM MCP West | PMW Top End-Katherine | 11/11/2019 0:00 | CTAP ID 1789 | 262 |
ProjeCTAPs | PM MCP West | PMW Top End-Katherine | 11/19/2019 0:00 | CTAP ID 3928 | 4 |
ProjeCTAPs | PM MCP West | PMW Tennant Creek | 3/6/2020 0:00 | CTAP ID 1780 | 127 |
ProjeCTAPs | PM MCP West | PMW Spencer Gulf | 11/20/2019 0:00 | CTAP ID 1778 | 434 |
ProjeCTAPs | PM MCP West | PMW Spencer Gulf | 12/9/2019 0:00 | CTAP ID 1778 | 202 |
ProjeCTAPs | PM MCP West | PMW Spencer Gulf | 2/11/2020 0:00 | CTAP ID 1778 | 151 |
ProjeCTAPs | PM MCP West | PMW Spencer Gulf | 2/26/2020 0:00 | CTAP ID 1778 | 4 |
ProjeCTAPs | PM MCP West | PMW Spencer Gulf | 3/3/2020 0:00 | CTAP ID 4667 | 71 |
ProjeCTAPs | PM MCP West | PMW South East | 11/29/2019 0:00 | CTAP ID 1727 | 2 |
ProjeCTAPs | PM MCP West | PMW South East | 12/9/2019 0:00 | CTAP ID 1727 | 4 |
ProjeCTAPs | PM MCP West | PMW South East | 1/2/2020 0:00 | CTAP ID 2605 | 96 |
ProjeCTAPs | PM MCP West | PMW South East | 2/26/2020 0:00 | CTAP ID 1380 | 40 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 1/1/1980 0:00 | CTAP ID 835 | 16 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 12/13/2019 0:00 | CTAP ID 2167 | 22 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 12/24/2019 0:00 | CTAP ID 835 | 122 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 12/31/2019 0:00 | CTAP ID 835 | 23 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 1/16/2020 0:00 | CTAP ID 835 | 17 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 1/20/2020 0:00 | CTAP ID 835 | 118 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 1/24/2020 0:00 | CTAP ID 835 | 79 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 1/31/2020 0:00 | CTAP ID 3494 | 9 |
ProjeCTAPs | PM MCP West | PMW Pilbara | 2/15/2020 0:00 | CTAP ID 2497 | 32 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 2242 | 42 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 2242 | 132 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 2242 | 276 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 2242 | 317 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 2242 | 349 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 2242 | 422 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 3045 | 142 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 3045 | 299 |
ProjeCTAPs | PM MCP West | PMW Perth | 1/1/1980 0:00 | CTAP ID 3045 | 336 |
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
Solved! Go to Solution.
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]))
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])
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 🙂
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"
)
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]))
HI Amitchandak,
I have created the below two measures :
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
😐 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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
108 | |
64 | |
60 |