Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, in this example I have two ways of approaching just not sure whats easiest
Lets say I have 5 locations, A-E.
My table 'Transactions', records daily transaction amounts for all locations like this
What I want to do is rank the averge number of transactions for these locations. However, the problem is that for locations A, B, C and D, the goal is 500. For E (Since its larger) the goal is 750. Note: the goal is to get as close to the target as possible. Meaning too many is bad on costs (i know that doesnt make much sense but this is a dummy example for what im actually doing) and too little is also bad.
At a first glance this is really simple where all I would have to do use my average measure
Average Transactions = AVERAGE('Transactions'[Sales])
And then find difference from my target
Variance = ABS([AverageTransations] - 500) and then sort by largest difference.
The issue is that for location E I need to do this by 750.
Option 1: make an if statement, but when i do a measure it doesnt recognize location column and when I do a calculate column it doesnt calculate correctly.
Option 2: enter table manually and create a relationship
Location Target
A 500
B 500
C 500
D 500
E 750
Both have failed. Any ideas?
Solved! Go to Solution.
Hi, @water-guy-5
You can try the following methods.
Column:
Target = LOOKUPVALUE(Target[Target],Target[Location],[Location])
Measure:
Variance = ABS(AVERAGE('Table'[Sales])-SUM('Table'[Target]))
Rank = RANKX(FILTER(ALL('Table'),[Date]=SELECTEDVALUE('Table'[Date])),[Variance],,DESC)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @water-guy-5
You can try the following methods.
Column:
Target = LOOKUPVALUE(Target[Target],Target[Location],[Location])
Measure:
Variance = ABS(AVERAGE('Table'[Sales])-SUM('Table'[Target]))
Rank = RANKX(FILTER(ALL('Table'),[Date]=SELECTEDVALUE('Table'[Date])),[Variance],,DESC)
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
179 | |
108 | |
104 | |
71 | |
70 |