Measure Total, again
Hi, this is my third day struggling with measure total, time to get some help. I have 3 columns in my matrix tabel that should be summarized row by row, and then ofcource sum up everything in the total. Column 1 [PY Actual] is Value from my fact table, column 2 [Inflation_basic] is a value from Inflation table, this tables have dates and Items. Column 3 [Inflation Effect] is Column 1 * Column 2 and gives me correct row value but then sum Total * Total and thats were the problem is. Item and Calendar is the dim tables. I have try summarize, summarize with sumX, hasonevalue, but no luck.
Rank/RankX of Measure in a Matrix
I am trying to use a scatter plot to draw a "periodic table" of asset class return by calendar year (similar to the target output picture). In order to do so, I need to first rank the returns in each calendar year. I tried using RANKX but I could not get the expected results (in green letter). Can you shed me some light how can I fix my DAX code please? I have attached my Power BI file in the link below. Thanks in advance. My Pbix file: https://limewire.com/d/BpJDt#VVxFzw3wCR
combinatorics in dinamic dax
Hello guys, i have posted here so many times about getting a logic right, but i just noticed it was impossible, in the way i was thinking, no one could help me. I have a new aproach of how to get the desired result, its by using combinatorics, of course, i need to know if its even possible in the dax. I have the image bellow that shows how the combinations might work, and ahead i also have the code i'm trying, its not working properly and i would like some help with it. In this code, the objective is have at the end the combinations that attend to the condition that the amount of concatenated orders is the highest and the sum of values are <= 21. Obs. this code might work with many different products, but to this example i just used one. Code: FinalCorrectStatus = VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto]) VAR OrdersTable = FILTER(ALL('DB_ORDERS'), 'DB_ORDERS'[Produto] = CurrentItem) VAR OrderCombinations = ADDCOLUMNS( GENERATEALL( OrdersTable, SUMMARIZE( OrdersTable, 'DB_ORDERS'[Pedido], "Soma_Quant_Falta", SUM('DB_ORDERS'[QT. falt]) ) ), "PedidosConcatenados", CONCATENATEX(OrdersTable, 'DB_ORDERS'[Pedido], ", "), "Qtd_Pedidos", COUNTROWS(OrdersTable) -- Conta o número de pedidos na combinação ) VAR FilteredCombinations = FILTER(OrderCombinations, [Soma_Quant_Falta] <= 21) VAR MaxPedidos = MAXX(FilteredCombinations, [Qtd_Pedidos]) -- Encontra o maior número de pedidos VAR BestCombination = FILTER(FilteredCombinations, [Qtd_Pedidos] = MaxPedidos) -- Mantém apenas a combinação com mais pedidos RETURN BestCombination Can anyone please help me with it?
Individual Row Colors in Matrix
Hello! I am attempting to color individual rows of a matrix. I understand this is not currently a feature in Power BI, but using this YouTube video, I was able to find a work around. I used the below measure, which I used as Conditional Formatting, to color the rows based on the HEX color listed in each row of the measure. However, this seems to exclude cells with a zero/blank value. How can I modify the below measure to ensure all cells for each row have the same formatting? For instance, "In Transit Receipts" shows many white cells because they have no value. I would like these cells to be the same light blue color as the cells with a value. Metric Colors = SWITCH( TRUE(), MAX('APO Monthly'[MetricNew])="Forecast","#d9e1f2", MAX('APO Monthly'[MetricNew])="Sales Order","#d9e1f2", MAX('APO Monthly'[MetricNew])="Global Sales Orders","#d9e1f2", MAX('APO Monthly'[MetricNew])="Global Forecast","#d9e1f2", MAX('APO Monthly'[MetricNew])="DI Forecast","#e6e6e6", MAX('APO Monthly'[MetricNew])="Stock Transfer (Out)","White", MAX('APO Monthly'[MetricNew])="SAP Suggested Buy","#e6e6e6", max('APO Monthly'[MetricNew])="In Transit Receipts","#d9e1f2", MAX('APO Monthly'[MetricNew])="Confirmed Receipts","#d9e1f2", MAX('APO Monthly'[MetricNew])="Additional Confirmed Receipts (Inc STO In)","#d9e1f2", MAX('APO Monthly'[MetricNew])="Total Demand","#09124f", MAX('APO Monthly'[MetricNew])="Total Receipts","#09124f", max('APO Monthly'[MetricNew])="Stock On Hand (Projected)","#055201", "White" ) Thank you!
Dax formula
Dear all, I'm new to the Power BI environment so be patient with me. I have created a report for my project financial overview. I have a matrix table where the rows are the project hierarchy (main and subs) in a nested layout. In columns i have the Contracted amount, Invoiced amount, budget, costs etc. Now i have created a measure that will calculate the unbilled amount based on my cost progress (if result=<0,0,result). My issue there is that in the total row of the table it will not sum the results of the lines (Green) but it will calculate the value based on the total line results (Red). Does anyone knows a trick or a different type of calculation i could make, in order to not change the visual i have currently?
Hide subcategories which have at least one blank value in matrix column
Hello! Could you please help me to solve the problem? I got matrix visual, in rows - categories and subcategories, in columns - month number in year, in values = average amount of sold products. As you see not all subcategories have sales in every month. So i would like to hide these kind of categories dynamically. I have tried to set a filter on visual "Metric does not equal blank" but it hasn't worked. I attached matrix example and measure i use: AVERAGE ( 'fct_table'[ValueColumn] )
Matrix Grand Total Changing As Rows Are Added/Removed
Wondering if anyone has come across an issue like this - I have a matrix visual with a field parameter as the row so users can add or remove dimensions similar to a pivot table. The dashboard contains sales data and is used to compare sales in one period to another. The data model looks like the image below. Basically a fact table connected to 2 date dimensions, one indirectly, and several other dimensions. The baseline period sales is normalized to the comparison period using sales per day with the measure below (# of days are variables): BaselineSales = DIVIDE(CALCULATE(SUM(Fact_Sales[Sales]), ALL(Dim_Date01), USERELATIONSHIP(Fact_Sales[SalesDate], Dim_Date02[Date])), _BaselineDays) * _ComparisonDays The problem I'm having is that when dimensions are added or removed to the row of the matrix, the baseline sales values are changing. I'm pretty sure it has something to do with the filter portion with the ALL and USERELATIONSHIP because the values are not changing when I remove that portion. Does anyone know a solution to this?
Avoiding displaying duplicate values for parent child items in a matrix
I have a dataset that includes goals and multiple options for each goal. Each goal has a target value and each option has an estimate. There is a one to many relationship between goal and option. Trying to figure out how not to repeat the 'Goal Target' for each Option in the simplified matrix below. Basically I want the opposite of Don't Show Subtotals, where I show the Subtotal of 'Goal Target' for each Goal but don't show the lime item for each Option. Putting this under DAX but would welcome data model or visual option solutions. Can't figure out how to upload a file so...
