Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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
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
Is this data in 1 table or 2?
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |