How to sum a rate from a dimension table only for the keys present in my fact table?
Hi Power BI Community, I have a classic star schema model with a fact table and a dimension table. My dimension table includes a numeric rate/price field (DiscountPlanEffectivePrice) and a key called DateDiscountPlanMeterId. This key is also present in my fact table, and it's used for joining the two tables. In my matrix visual, I’m grouping by various topology fields (like region, offer, pricing model, etc.) from my fact table, and I want to display the sum of the unique DiscountPlanEffectivePrice values from the dimension table, but only for the keys that actually exist in the current filter context of my fact table. My goal is to: Avoid summing all rates from the dimension, and Avoid duplicating the same rate for every related fact row. I’ve tried DAX measures like: Sum of Effective Discount Prices = SUMX ( VALUES(Fct_EA_AmortizedCosts[DateDiscountPlanMeterId]), CALCULATE( SELECTEDVALUE(Dim_EA_AmortizedCosts_DiscountPlans[DiscountPlanEffectivePrice]) ) ) and also attempted using SUMMARIZE, but I’m either getting blank results or incorrect totals. The relationship between the tables is active and based on DateDiscountPlanMeterId. How can I correctly sum only the unique rates from the dimension for keys that are present in my fact table’s current context? Is there a better DAX pattern, or am I missing something in the way context or relationships should be handled for this scenario? Any suggestions or guidance would be greatly appreciated! Thanks in advance! Jacek
My Matrix Table Keeps Showing More than X rows despite Measures - How to fix?
Hi - Currently my table (named Overview) is displaying Sites (via distinct site table) and Customers (via distinct customer table) as the Matrix Table's rows and the overall store sales + top 5 customer sales (depending on the time period picked). Data sheet hyperlink PBIX file hyperlink I'm experiencing a problem where: 1. I can't seem to make a Top/Bottom 20 stores slicer (based off sales) 2. Adding Date columns to my Matrix Table rows removes my top 5 Customers row condition and instead shows 6 or more customer rows/exceeds query resources To illustrate what i'm doing currently - I made the table's sales value by these measures: Total Sales = SUM( Overview[Sales]) Then i used this measure as the Matrix Table value: MEASURE-SALES = IF( ISINSCOPE( 'Unique Customers'[Customer] ), [Total Sales], CALCULATE( [Total Sales], REMOVEFILTERS( 'Unique Customers'[Customer] ) ) ) As mentioned before - i also made the table show each store's top 5 customers via this rank: Customer Rank = VAR BaseTable = ADDCOLUMNS( CALCULATETABLE( SUMMARIZE( Overview, 'Unique Customers'[Customer], 'Unique Stores'[Store] ), REMOVEFILTERS( 'Unique Customers' ) ), "@val", [Total Sales] ) VAR Result = RANK( SKIP, BaseTable, ORDERBY( [@val], DESC ), PARTITIONBY( 'Unique Stores'[Store] ) ) RETURN Result then i set this measure as = 1 in the table's filter Customer is visible = VAR CustomerRank = [Customer Rank] VAR Result = IF( ( CustomerRank <= 5 && NOT ISBLANK( CustomerRank ) ) || NOT ISINSCOPE( 'Unique Customers'[Customer] ), 1 ) RETURN Result My date table is below for reference (capped at current month for YOY measure purposes not mentioned above): Date = ADDCOLUMNS ( CALENDAR ( DATE (2023, 1, 1), DATE (2025, 4, 30) ), "Year", YEAR([Date]), "Year-Month", FORMAT([Date], "yyyy-MM"), "Year-Month sort", EOMONTH([Date], 0) ) Would like if there was a test file sample on this if possible as i keep getting stuck/written measures i recieve return an error- the common problems i'm running into for 2. as mentioned before is: - Query exceeds resource when Year-Month is added - Rows exceed 5 customers when "Year" column is added / time range on slicer is picked
Deltas within a Measure (?)
Hello all, again, I need help because I am totally lost: with the help of this forum, I was able to create this chart, and there is now only missing, to show the deltas between the periods (-15% between 2020 and 2021 i.e.). But I do not have an idea, how to calculate it, because to be able to create that chart, I have the values in one measure: YaQ_Volume = SWITCH( SELECTEDVALUE('Unrelated Date'[Category]), "Year",CALCULATE([SalesFinal],FILTER(VALUES(Output[Yr]),FORMAT(Output[Yr],"#")In VALUES('Unrelated Date'[Period]))), "Quarter",CALCULATE([SalesFinal],KEEPFILTERS(TREATAS(VALUES('Unrelated Date'[Period]),Output[QYear])), KEEPFILTERS(Output[Yr]=MAX(Output[Yr])))) Is there a way to insert now also the deltas between the periods? Thank you for any tipp and advice.
Show latest value based on date and other criteria (measure or new column)
Hi, I am trying to create a measure or column that will allow me to easily bring the latest value into my visuals. Here is some example data; the highlighted column is what I'm trying to create. The end goal is to show the most recent sale as a flat line across a graph, so I can compare historical sales to the most recent one visually, by product. Also, the date needs to be dynamic so it automatically updates the values when fresh data is loaded. Thanks!Subtotal and totals not computing correctly
I have two tables and want to use the values in the table/column 'JCCD'[ActualCost] except when in the values in table/column 'bihj HeavyJob_CostSummary'[HJCost] are higher than in the 'JCCD'[ActualCost] table/column only when 'HeavyJob_CostSummary'[LMSEO] or 'JCCD'[LMSEO] has a value of "Labor" or "Material". This works with the following: NewColumn = IF( [HJCost minus ActualCost] > 0, SUM('bihj HeavyJob_CostSummary'[HJCost]), SUM('JCCD'[ActualCost]) ) or: NewMeasureCO = VAR LMSEO_Value = SELECTEDVALUE('bihj HeavyJob_CostSummary'[LMSEO]) VAR HJCost = SUM('bihj HeavyJob_CostSummary'[HJCost]) VAR ActualCost = SUM('JCCD'[ActualCost]) RETURN IF( LMSEO_Value IN {"Labor", "Material"} && HJCost > ActualCost, HJCost, ActualCost ) However, when you subtotal or total all the values they are not correct. How do I get the subtotal and total to compute the correct value?
