Forum Discussion
Figuring out this LookUp Formula
JChouris By default, slicers affect every other visual on the page. So, for example, if you select a part number of TCRH and an Invoice Year of 2022 in your slicers then only the first row of your table will be in context for the visuals on the page. This means that if you do a SUM of 3 Year Combined RGM, then the value returned would be 478 because only the first row is in context.
If you need to override the default context for something then you can use the ALL function to override the filters from the slicers.
JChouris You can use MAXX( FILTER( ... ), ... ) instead of LOOKUPVALUE and you could use the IN operaor in your FILTER statement which would refer to your slicer column. Not sure if I am completely understanding what you are trying to accomplish though.
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
| PartNum | InvoiceYear | 3 Year Combined Shipped | PartNum | RGMYear | 3 Year Combined RGM | |
| TCRH | 2022 | 292 | TCRH | 2022 | 478 | |
| TCRH | 2023 | 929 | TCRH | 2023 | 876 | |
| TCRH | 2024 | 1891 | TCRH | 2024 | 1130 |
I have formulas in place to calculate the 3 year running total for RGM units and Shipped units.
I want to be able to use the slicer to filter to the part number i want, the RGM Year and Invoice Year. What i am falling short on is the bar graph at the top and the forula for that. I need to Take the total Generated by the 3 Year Combined RGM and Divide it by the total for the 3 Year Invoiced to get my values in the graph. But i am unsure how to get the graph to reflect the choices on the slicer and use those values as the totals to divide. I hope that makes sense. I am fairly new to this and havevnt worked much with calculations like this.
JChouris By default, slicers affect every other visual on the page. So, for example, if you select a part number of TCRH and an Invoice Year of 2022 in your slicers then only the first row of your table will be in context for the visuals on the page. This means that if you do a SUM of 3 Year Combined RGM, then the value returned would be 478 because only the first row is in context.
If you need to override the default context for something then you can use the ALL function to override the filters from the slicers.