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.
Seems like a simple thing, but am looking for some help! I need to return a value in the "GC Scenario" column equal to the value of column three (94,340.75) times the percentage ("value" - second screenshot) from another table.
The ".25" can vary month to month and all our efforts thus far have calculated the entire column. What's the best expression to capture the single row?
Visual:
Table:
Solved! Go to Solution.
Hi, @tcboutte
You can try a Measure like the following.
GC Scenario =
VAR charges_sum =
CALCULATE ( SUM ( Financial[Charges] ), DISTINCT ( Financial[Person] ) )
VAR val =
CALCULATE (
ROUNDUP ( MAX(Percentage[Value]) , 2 ),
FILTER ( Percentage, Percentage[Date] = MAX ( Percentage[Date] ) ),
TREATAS ( VALUES ( Financial[Person] ), Percentage[Person] )
)
RETURN
charges_sum * val
Here is the pbix.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @tcboutte
You can try a Measure like the following.
GC Scenario =
VAR charges_sum =
CALCULATE ( SUM ( Financial[Charges] ), DISTINCT ( Financial[Person] ) )
VAR val =
CALCULATE (
ROUNDUP ( MAX(Percentage[Value]) , 2 ),
FILTER ( Percentage, Percentage[Date] = MAX ( Percentage[Date] ) ),
TREATAS ( VALUES ( Financial[Person] ), Percentage[Person] )
)
RETURN
charges_sum * val
Here is the pbix.
Best Regards,
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That did the trick for us, thank you!!
What determines which row is to be used in both tables in the calculation?
Proud to be a Super User!
Paul on Linkedin.
The person's name, Chen Dean, in this case. Basically, this is to calculate the value of their revenue generation by using that variable percentage rate. We also want to keep the value accurate by month so that the historical records calculate correctly, even if the percentage changes over time.
How is your model set up? are the tables linked in a relationship? how are you going to show the result? a page with slicers for person's name and month? or do you only want to show the latest month's result? Is the revenue linked to a month?
Probably best if you provide a depiction of what you actually want to show..
Proud to be a Super User!
Paul on Linkedin.
Thank you Paul. The tables are linked by Name and Date. Also, the visual will show a "Top N" result and the only sliver will be to select a Month.
Here's an Excel mock-up:
Thank you for that! can you please share the sample data as a file by uploading it to a cloud service (Onedrive, Google Drive, Dropbox...) and sharing from there?
Proud to be a Super User!
Paul on Linkedin.
Thanks again, Paul. Here's the sample with everything pointing to a C:\PBI destination.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |