Figuring out this LookUp Formula

[JChouris]

I am building a tool to represent equipment durability. I am drawing RGM year and 3 year combined RGM count as well as Invoice Year and 3 year combined shipped quantity. Currently i have this formula but im trying to figure out the best way to let the slicers catagories control what the chart represents instead opf putting it directly in the formula like it is now. Any help would be greatly appreciated. Thank you.

New Count of RGM Date Year divided by Count of InvoiceDate =

DIVIDE(

LOOKUPVALUE('(OrderSearch) RGM'[Last 3 years combined], '(OrderSearch) RGM'[Unique Identifier RGM], "2024TCRH"),

LOOKUPVALUE('(OrderSearch) OrderLineItems'[Invoice last 3 years combined], '(OrderSearch) OrderLineItems'[Unique Identifier Order Line Items], "2024TCRH")

)

[Greg_Deckler]

@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.

[JChouris]

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

@Greg_Deckler 

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.

[Greg_Deckler · Accepted solution]

@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.