cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Casteless Regular Visitor
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
Highlighted
Community Support Team
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
Super User
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
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
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 

Highlighted
Community Support Team
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: | |

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 89 members 1,613 guests
Please welcome our newest community members: