cancel
Showing results for
Did you mean:
Casteless Regular Visitor

## 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

Accepted Solutions Community Support Team

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

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
4 REPLIES 4
Highlighted Super User

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

Is this data in 1 table or 2?

* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Casteless Regular Visitor

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

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,

Baskar Super Contributor

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

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

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

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

Announcements #### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. #### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work Top Kudoed Authors
Users Online
Currently online: 346 members 3,961 guests
Recent signins:
• giblet17 • PORCISAN • Alan_Silva_TI • cihan_yilmaz • basgeertmn • SamuelDrescher • vaibhavdesai • onthecover • ameetjeeva • MorrisKato • Virgie123 • brunelygrattz • KevinParijs • PowerBI-user20 