Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JS_pbi
Frequent Visitor

Measure to group and sum - perform calculation on subgroups - sum subgroups

I am trying to create a dynamic power curve regression but I need to do the following to generate the c and b of y=c*x^b which require the following steps:

 

So, can I do the following based on the  explanatory data below?

  1. Group by X and sum Y
  2. Extract the natural log from these aggregated subgroups of Y using LN function
  3. Sum the results of step 2

Expected Result = -41.93

 

There are other columns/dimensions which I will filter on and have the measure result dynamically change with the formula.

 

I have spent many hours researching this and all I have come to is the following formula which only generates the natural log of the sum of all values in the field - not the subsets.   

    Measure = CALCULATE(LN(SUM('Power Curve Test Data'[y])),'Power Curve Test Data'[x])

 

 

X              Y

00.16
10.1
20.07
30.06
40.058
50.052
60.042
70.041
80.038
90.032
100.029
110.027
120.022
130.019
140.012
00.116517
10.099084
20.027853
30.057962
40.025471
50.00566
60.017254
70.03229
80.0083
90.029146
100.00775
110.005638
120.019069
130.000277
140.005248
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

Hi @ JS_pbi You can use SUMMARIZE function AND ADDCOLUMNS function Try this formula please

Measure = SUMX(ADDCOLUMNS(SUMMARIZE('Power Curve Test Data','Power Curve Test Data'[X],"A",SUM('Power Curve Test Data'[Y])), "ln", ln([A])), [ln])

 

Result:

1.png

 

Best Regards,

Lin

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

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

Hi @ JS_pbi You can use SUMMARIZE function AND ADDCOLUMNS function Try this formula please

Measure = SUMX(ADDCOLUMNS(SUMMARIZE('Power Curve Test Data','Power Curve Test Data'[X],"A",SUM('Power Curve Test Data'[Y])), "ln", ln([A])), [ln])

 

Result:

1.png

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

can you explain this formula, I understood untill SUM part but after that where do "ln" and in([A]) came from 

This would be nice if you explain whole formula to make it easy to understand

Thanks

Thank You!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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