Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table like below:
Name | Group | Month | Amount |
Bob | Tennis | Jan | 100 |
Bob | Football | Jan | 50 |
Fred | Tennis | Jan | 50 |
Mary | Football | Feb | 40 |
Mary | Golf | Jan | 300 |
I need to sum each by name for each month, then flag them as paying over or under £100 for each month so I can then use this new flag/column in a filter (the group is irrelevant but this is how the table is aggregated as it's used for something else). Is there an easy way to do this? I was looking at a summarised table but thought it seems a bit overkill.
Solved! Go to Solution.
You can use the measure to filter a visual. Im not sure I understand what you need. If it's a calculated column in the table above, then:
Flag =
VAR amountCustomerMonth_ =
CALCULATE (
SUM ( Table1[Amount] ),
ALLEXCEPT ( Table1, Table1[Name], Table1[Month] )
)
RETURN
IF (
amountCustomerMonth_ > 100,
"Paying over 100 quid",
"NOT paying over 100 quid"
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
You can use the measure to filter a visual. Im not sure I understand what you need. If it's a calculated column in the table above, then:
Flag =
VAR amountCustomerMonth_ =
CALCULATE (
SUM ( Table1[Amount] ),
ALLEXCEPT ( Table1, Table1[Name], Table1[Month] )
)
RETURN
IF (
amountCustomerMonth_ > 100,
"Paying over 100 quid",
"NOT paying over 100 quid"
)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
I'm not sure who you're replying to. The solution above works as intended. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
It does for a measure, but I need to apply that to to a filter so I can filter between over £100 and under £100. I can't put a measure in a filter.
Many Thanks
@GlynMThomas , create these two new columns and use the flag as a filter
New column =
var _1 = sumx(filter(Table, [Name] =earlier[Name] && [Month] =earlier[Month]),[Amount])
return if(_1>100, 1, 0)
Flag =
var _1 = countx(filter(Table, [Name] =earlier[Name]),[New column])
var _2 = sumx(filter(Table, [Name] =earlier[Name]),[New column])
return
if(_1 =_2, 1,0)
Hi @GlynMThomas
1. Place Name and Month in a table visual
2. Create this measure and place it in the visual
Flag =
IF ( SUM ( Table1[Amount] ) > 100, "Paying over 100 quid" )
This will show only the rows meeting the condition
You can also do this through a calculated table
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thanks, I've already done this column but it doesn't take the fact the value is split by the Group column as well. I want it to sum by the month for each person and work out if they paid over 100 in that month. For example, Bob paid £150 in Jan so should be flagged as paying over £100, however this wouldn't flag him as over £100 as it seeing the rows as £50 and £100.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |