Not quite understanding Totals In Matrices
Hello my wonderful Power BI Community, I've run into a bit of an issue I'm hoping you all can share some light on. I've looked everywhere but have failed to find anything that is remotely successful. I'm currently working on a pricing report. The report utilizes a singular fact table of invoices stored in our SQL database which is queried by PBI twice for different results. The first query is called "Base Period" which is anything with an invoice date < January 01 of current year. The other table is called "Current Period" which is anything >= January 01 of current year. I then join these two tables to a bunch of Lookup tables like customers, items, etc. on their associated keys to get various fields. For example, LU Customer joins to both base period and current period on Customer_Key. LU Item joins on Item_Key, etc. Notably I have a fiscal calendar table joined ONLY to the current period table since I want people to be able to slice invoices in the current year by period number, but still see the average price of the base period as a full year calculation. See structure below: I've then created a matrix with Company, Customer, Product Category, and Item Code in the rows. I've then created a bunch of measures to calculate various fields. I'll provide the general idea along with the measure text in red or a screenshot. Further below is a screenshot of the created matrix so you can follow along. - Average selling price (ASP) in the base period --> Average Selling Price Base Period = average('Base Period'[LocalUnitPrice]) - Average selling price (ASP) in the current period --> Average Selling Price Current Period = average('Current Period'[LocalUnitPrice]) - Price Traction P1 is the first part of the price traction $ equation. It takes the Current Period ASP and subtracts the base period ASP --> Price Traction P1 = [Average Selling Price Current Period] - [Average Selling Price Base Period] - Total quantity is simply the sum of the quantity invoiced from the current period --> Total Quantity = sum('Current Period'[QuantityInvoiced]) - Price traction $ is the Price Traction P1 multiplied by the total quantity. I've thrown in an IF to make sure that the price traction $ is 0 if either of the ASPs are blank (code in sceenshot below). You'll see further down in the screenshot of the matrix that at the item code level (1901x, 894520, etc.), this calculates correctly. However, the totals need to be the sum of these values rather than the total line P1 * total quantity. This is where the price traction fix below comes into play. - Price Traction Fix is my culmination of hours of research and trying all kinds of things, however it still doesn't work. The equation simply does a sumx based on my LU_Item[Item Code] and the price traction $ measure (see code below). This was ripped straight from an article I read and has gotten me closest to what I was trying to achieve. Now the problem arises when you look at the bottom screenshot: We can see the faulty Price Traction $ not doing what I want it to do. The price traction fix however seems to fix it until i go to the upper most total where I'm $246.53 off. If I sum 1901x and 894520 for each customer (Grainger and Airgas) I get the correct amounts, but then those amounts don't sum correctly to the company level at the top. The only other filters i have applied are: A base period year which limits the base period table to only show me the prices I want to compare to A customer filter to limit the data I'm seeing in my visual A company filter to limit the data I'm seeing in my visual I'd appreciate any help anyone is willing to give! Thank you for time and energy! Have a blessed day. Sincerely, Nick
Disable Total for a text column
Hello All, I'm trying to hide the total of a text column that is using aggregation like first or last value it is showing the text in the Total row. I have disable/hide the text from the Total. Can we achive this? Visualization used: Table I tried implementing steps from this forum but they're not satisfying my requirement. Solved: Disable total of text column in Grand Total. - Microsoft Fabric Community I tried implementing this measure, however it does the job partially. But if the text column has two values the row-level results returns a blank. Text Measure = IF ( HASONEVALUE ( Table[Columnonrow] ), MAX ( Table[TextColumn] ) ) Also as the text values are too lengthy so the field formatting does not work. eg: Any help is appreciated. Thanks!
How do I calculate an extra colomn of Percentage of Grand total with dax
Hello guys, In the third colomn you can see the % of GT, this is done by "show value as". I want this in a measure so I could calculate some formulas. The column "Total90%FilterCalculate" is created with this measure: Total90%FilterCalculate = CALCULATE(DISTINCTCOUNT(MKA[Versies.Id]), FILTER(MKA, MKA[VullingTotaalPerc]="90 %")) How do I create this measure? Thnx!How to add a measure's result of many lines
Hi everyone! I am currently working on a table that relates the amount of components that my company receives and returns. Here is how the table is at this moment: "Cod. Forn. Principal" is an ID. "Entradas_bus" are the received components. "Refugos_bus" are the components we have returned. "Impacto_PPM" is calculated by a measure. It's the returned pieces of one ID divided by the SUM() of all received pieces during an interval of time. "Porcentual PPM" is the % that each line represents in the total of 'Impacto_PPM'. Basically, I want to know how much % does 829,27 represents out of the total 'Impacto_PPM'. Right now I'm struggling to calculate the "Porcentual PPM" because I can't find a way to get the summation of all lines in the "Impacto_PPM" column. can somebody help me pleaseCumulative measure with group by
Hi dears, I'm trying solve a problem with a measure I create to generate a total register by user and month/year and create a burndow graph. So, in month like june 2022, when I don't have any register of my users, the cumulative measeure return a 0 values instead return the last value. Below I sent the measure: _TotalParcialReunioes = COUNTROWS( FILTER( GROUPBY( CALCULATETABLE( 'DadosReuniões', FILTER(ALLSELECTED('DadosReuniões'),'DadosReuniões'[MesAno] <= MAX('DadosReuniões'[MesAno]) && 'DadosReuniões'[Encontro realizado] = TRUE())), 'DadosReuniões'[Nome Funcionário], "QTDE_REUNIOES", COUNTX(CURRENTGROUP(),'DadosReuniões'[Nome Funcionário]) ), [QTDE_REUNIOES] >=1 ))+0 How can I reapeat the last value, when the month no has any data? ThanksTotal (sum) of a measure
Hello, I need help to change the total of these measures. To explain I already done a measure that multiply a price by a quantity, FY19-20 x Qté composants (UQB) = SUM('Fichier Acheteur'[FY19-20]) * SUM('Mapping'[Qté composants (UQB)]) the probleme is that the total multiply the final price by the quantity too, I just a need a sum of the column in the total.
Measure total giving wrong result - how to fix?
I have a measure which gives a total of working days (it takes the possible working days in a month and subtracts any holiday or sickness etc. Working days = ('Dates'[working days])-CALCULATE(SUM(Leave[days off])) It works fine line by line in a table, but the totals are wrong. I assume it is because it is across two table and therefore the total has no context - but I have no idea how to add it! How do I make a measure to get the total please? I am a relative beginner so than you in advance for your patience and simple terminology 🙂DAX - Add column referring to a measure.
Hello, I need to add the values that are marked in the column to the side. The column "New Bimonthly Customers" derives from a measure created: Novos Clientes Bimestrais = VAR AllOldCustomers = CALCULATETABLE ( VALUES ( SBOPRODMS[Nome do PN] ), SBOPRODMS, SBOPRODMS[Data NF] < TODAY () - 60, SBOPRODMS[Documento] = "Nota fiscal de saída" ) VAR AllNewCustomers = CALCULATETABLE ( VALUES ( SBOPRODMS[Nome do PN] ), SBOPRODMS[Data NF] < TODAY () , SBOPRODMS[Documento] = "Nota fiscal de saída" ) RETURN COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )
