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
Casteless
Helper I
Helper I

Calculate a Variable and then use that number as a constant to be applied to other rows?

So I have data structure like so:

 

Name     |    Number

 

Group1  |     150

Rep1      |    25

Rep2      |    30

 

Group2  |    126

Rep3      |    40

Rep4      |    20

 

I'm trying to split the value of group 1 between the reps within that group in a measure to get

 

Rep1   |    90 

Rep2   |    105

Rep3   |   103

Rep4   |     83

 

I tried to do this using a variable in a measure like so:

 

Measure = 
var group1total = CALCULATE(VALUES([Number]),[Name]="Group1")
var numberofreps = CALCULATE(COUNTROWS([Name]),Group="Group1")

Return Calculate([Number]+group1total/numberofreps)

When I put this in a table it calculates the value that i want but only in the row for Group1

 

Is there a way to calculate a variable like "group1total/numberofreps" and then treat that number as a contant? so that filters in visuals arent applied to it?

 

 

 

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi  @Casteless,

 

You can try to use below formula to get the average value of current group.

 

1. Add a column to store the current rep's group and merge these tables.

 

Table = UNION(ADDCOLUMNS(Table1,"Group",if([Name]<>"Group1","Group1",BLANK())),ADDCOLUMNS(Table2,"Group",if([Name]<>"Group2","Group2",BLANK())))

 

 

2. Write a measure to calculate the average of current group.

 

avg of current group = 
var currGroup= if(LASTNONBLANK(Table[Group],[Group]) <> BLANK(),LASTNONBLANK(Table[Group],[Group]),LASTNONBLANK(Table[Name],[Name]))
var total= LOOKUPVALUE(Table[Number],Table[Name],currGroup)
var countOfRep= COUNTROWS(FILTER(ALL(Table),Table[Group]=currGroup))
return
total/countOfRep

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi  @Casteless,

 

You can try to use below formula to get the average value of current group.

 

1. Add a column to store the current rep's group and merge these tables.

 

Table = UNION(ADDCOLUMNS(Table1,"Group",if([Name]<>"Group1","Group1",BLANK())),ADDCOLUMNS(Table2,"Group",if([Name]<>"Group2","Group2",BLANK())))

 

 

2. Write a measure to calculate the average of current group.

 

avg of current group = 
var currGroup= if(LASTNONBLANK(Table[Group],[Group]) <> BLANK(),LASTNONBLANK(Table[Group],[Group]),LASTNONBLANK(Table[Name],[Name]))
var total= LOOKUPVALUE(Table[Number],Table[Name],currGroup)
var countOfRep= COUNTROWS(FILTER(ALL(Table),Table[Group]=currGroup))
return
total/countOfRep

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Is this data in 1 table or 2?



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Its in two 

 

one defines Reps/Groups by group

 

Group1: Rep1, Rep2, Group1

 

Group2: Rep3, Rep4, Group2

 

the second lists the groups and reps -together-

 

Name/Number

Group1/##

Rep1/##

Rep2/##

Group2/##

Rep3/##

Rep4/##

 

 

I got it to work by creating yet another set of tables but i would very much prefe rnot to have to do that for every metric I use with this data set up so if there is a way to do it in a measure that would be amazing.

 

cheers,

Can u please share some sample table structure. it will help us to clear 

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.