Slow DAX formula to calculate # Orders Delayed with adjustment
Hi I have a datamodel with orderlines and goods receipt. I have written a formula to calculate number of unique orders delayed (in Danish # Ordre Forsinket) based on two what if parameters. The parameter 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat] is used to adjust the delay with X number of days on those orders there are marked as indkøbsbilagsart ZCD or ZLM. The parameter 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat] is used to adjust the delay with X number of days on those orders there are different from indkøbsbilagsart ZCD or ZLM. The data model looks like this and there is 1.4 million rows in the table 'Ordrelinjer' and 2.775 suppliers in the table 'Leverandør': Here is the DAX formula: # Ordre Forsinket = VAR _LeveringstidRegionslagerRabat = SELECTEDVALUE( 'Leveringstid Regionslager rabat'[Leveringstid Regionslager rabat], 0 ) VAR _LeveringstidOevrigeRabat = SELECTEDVALUE( 'Leveringstid Øvrige rabat'[Leveringstid Øvrige rabat], 0 ) RETURN CALCULATE( DISTINCTCOUNT('Ordrelinjer'[Indkøbsordrenummer]), FILTER( ADDCOLUMNS( FILTER( 'Ordrelinjer', 'Ordrelinjer'[Varemodtagelse] = 1 && 'Ordrelinjer'[Afvigelse i dage] > 0 ), "JusteretAfvigelse", 'Ordrelinjer'[Afvigelse i dage] - IF( RELATED('Indkøbsbilagsart'[Indkøbsbilagsart]) IN {"ZCD", "ZLM"}, _LeveringstidRegionslagerRabat, _LeveringstidOevrigeRabat ) ), [JusteretAfvigelse] > 0 ) ) The measure is used in a table visual, but it takes around 33 seconds to refresh the table visual. I need a few more measures in the table, so it will just take longer and longer time to refresh the table. Can I write the DAX formula in a more efficient, clean and performance optimized way? Best regards Morten
Table Visualization by Customer breakdown by Type and Month
Hello Power BI Community Can anyone please advise me on how to resolve this. My use-case is that we have monthly finance transactional data for each month from Jan to May (e.g. Data source is SQL), bringing in the latest data every month. My Datasets each row consists of Month, Customer, Brand, Sales Price, and Volume and Data model consist of DateTable, where the month has one to many relationships with Customer Table. I manage to create DAX showing MTD & YTD calculation for [Volume], [GM], [ARP £/MT]. Please note [Volume], [GM], [ARP £/MT] these are measures created from monthly finance transactional data. I tried Summarise and Add column DAX nothing seems to be working out, may be i'm made some error. DAX Measures for Volume = Sum('Gross Margin'[Volume]) DAX Measure for ARP£/MT = (DIVIDE([Sale Price],[Volume])) My final output would like to plot the data on a column chart by Type (Volume], ARP £/MT], [GM £/T] the columns would be stacked in each month to give the break-down by the customer by Brand. I want to create two table visualization one by MTD and another Cumulative total, without giving the grand total for ARP £/MT (Average Retail Price) & GM £/ MT, something like this (above mentioned format). Appreciate your help in advance.😊
Aggregated values directly at the last rows of TABLE visuals
Hi, Being a newbie requesting help to get the aggregated values below the "total" row generated from TABLE visual. Unable to use MATRIX visual as i need to show the aggregated values below the total(sample representation as below).Will use the same for Date wise as well as month wise ,year wise slicer. Data Set Sample as below: Also i have created Aggregated Table using Power query which is available as a data set: Also have created measures on values for each measuing point separately which is used for other graphical visuals. Deeply appreciate forums support to meet the objective. Thank you.
Creating excel like cell-based dropdown in Power BI Table
Hi, I am new to Power BI and was looking for ways to create a status update table in Power BI to track progress of certain tasks. The table in excel would look like this I want to create a similar table where I would be able to update the status in the visuals itself. Is that possible to do in Power BI? Thanks in advance for any help!
Aggregate measure on two field of a visual table
Hi, I'm trying to replicate this table format on PowerBI table visual: I have created Measure1 as follows: Measure1 = CALCULATE(COUNT('Table'[Request Number])) and I put it on G column that is the value of each Field2 in column F in each month. What I want to do is to calculate column E by the same measure grouped by Field1 (column C) for each month. So for example on the first row of column E we have 11 for Field1=X and Month=January, that is 6+4+1 ofthe first three rows Column G, and so on. I tried the GROUPBY or ALLEXCEPT functions by Month and Field1 but it didn't work. How can I build the formula or use particular view for the visual to aggregate the column E by month and Field1?Power BI Visual hyperlink with parameters to display content in another visual
I'm wondering if it's possible to have a value in a table visual by a hyperlink with dynamic parameters that will populate another table visual when clicked? For example, table 1 has colums of "item desc", "date", "qty sold", "qty returned" with rows of "pink bikes", "2022-08", "5", "3"; "pink bikes", "2022-07", "8", "1"; "blue bikes", "2022-08", "18", "2". When the second row , column "qty sold" of "pink bikes" is clicked, table 2 visual would display the customers who bought pink bikes in July of 2022. But, if they click second row, column "qty returned", table 2 visual would display the customers who returned pink bikes in July of 2022. The first table is rolled up within the underlying sql, so I'd need to pull from a separate underlying data table with the information. Any ideas on how to do this? I'm happy to get into a video that's already available.Disable table visualization's order
hi, i have an excel table like this: Metrics Figures Loan 12 Provision 3 Write-offs 1.5 Net losses 2 I want my table visual to look exactly the same as in the above table. However, PB automatically sort the table based on the Metrics column. The output looks like this: Metrics Figures Loan ... Net losses ... Provision ... Write-offs ... How may I fix this issue? Thank you in advance!
