Summarize table Lookupvalue in Funnel Visual
Hi Everyone, i have the below summarized table "Cover Type Analysis" as per the below : CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B','Production ReportV20-V1 Power B'[Cover Type],'Production ReportV20-V1 Power B'[Transaction Year],"Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium])) i have created a new column LastYear: LastYear = IFERROR(FORMAT(CoverTypeAnalysis[Transaction Year],"General Number") - 1,0) i need to calculate PercentageYearlyGrowthByCoverType: PercentageYearlyGrowthByCoverType = DIVIDE(CoverTypeAnalysis[Total Premium] - LOOKUPVALUE(CoverTypeAnalysis[Total Premium],CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100 i have Transaction Year a Slicer Filter = 2019, everything looks wonderfull and the Funnel is looking correct: I was asked after that to add Quarter and Month Filters to this report, i added them to the summarized table : CoverTypeAnalysis = SUMMARIZE('Production ReportV20-V1 Power B', 'Production ReportV20-V1 Power B'[Cover Type], 'Production ReportV20-V1 Power B'[Transaction Year], 'Production ReportV20-V1 Power B'[Transaction Quarter], 'Production ReportV20-V1 Power B'[Transaction Month], "Total Premium",SUM('Production ReportV20-V1 Power B'[Cedent Premium])) and adjusted PercentageYearlyGrowthByCoverType to be as per the below: PercentageYearlyGrowthByCoverType = DIVIDE(CoverTypeAnalysis[Total Premium] - LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter], CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type]), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],CoverTypeAnalysis[LastYear], CoverTypeAnalysis[Transaction Quarter],CoverTypeAnalysis[Transaction Quarter], CoverTypeAnalysis[Transaction Month],CoverTypeAnalysis[Transaction Month], CoverTypeAnalysis[Cover Type],CoverTypeAnalysis[Cover Type])))/100 however im getting wrong results as if it excecute the formula for each row and at the it sums all percentages while what i want is to make the calculation for all selected values: also i tried to create a mesure but it returned empty and couldnt load it into Funnel Visual PercentageYearlyGrowthByCoverType mesure = DIVIDE(SUM(CoverTypeAnalysis[Total Premium]) - LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]), CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]), CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]), CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type])), ABS(LOOKUPVALUE(CoverTypeAnalysis[Total Premium], CoverTypeAnalysis[Transaction Year],SELECTEDVALUE(CoverTypeAnalysis[LastYear]), CoverTypeAnalysis[Transaction Quarter],SELECTEDVALUE(CoverTypeAnalysis[Transaction Quarter]), CoverTypeAnalysis[Transaction Month],SELECTEDVALUE(CoverTypeAnalysis[Transaction Month]), CoverTypeAnalysis[Cover Type],SELECTEDVALUE(CoverTypeAnalysis[Cover Type]))))/100 appreciate your urgent assistance Thank you in advance Best regards, Georges Sabbagh
7-day rolling sum issue with slicer
Hi everyone, I am having some issueswith my DAX code. I have an Excel table named 'MyTable' that I use as a dataset. In this table there are several columns, including one for dates (from 2019 to 2021) and one for the number of orders I have per day. I wanted to create a 7-day rolling sum the returns for a given day the number of orders for the current day and the 6 days before. Here is an example: Date Orders 7-day rolling sum for orders Jan 1st 5 5 Jan 2nd 2 7 Jan 3rd 6 13 Jan 4th 3 16 Jan 5th 7 23 Jan 6th 4 27 Jan 7th 2 29 Jan 8th 3 27 Here is an example of the kind of results I am supposed to get. Here, for Jan 8th, I should get the sum of Jan 8th + the 6 days before. My DAX formula is the following: Orders 7DR Sum = CALCULATE( SUMX('MyTable',MyTable[Orders]), DATESINPERIOD(MyTable[Date], LASTDATE(MyTable[Date]), -6, DAY) ) Technically it works if I show the entire data on my Power BI dashboard. However, I need to use slicers on my dashboard to filter the data. The problem with the slicer is that if I ask it to filter the data for february 2019 for example, the formula will start the sum at the beginning of february. Leading to the first 7 days of the chart being wrong. How can I modify my DAX formula so that it still considers the dates outside of the slicer? Thank you very much.
calculate time difference between two timstamp columns in direct query mode in PowerBI
I need to calculate the time difference between two timestamp columns in my table and create a new column with the calculated values but as I'm doing this in the direct query mode, I have some limitations for it. I tried datediff, duration.totalseconds etc but nothing really worked.. the result I want is I got this result using the sql below select (unix_timestamp(timestamp1)-unix_timestamp(timestamp2))/3600 as hourly_diff from azure_anomaly_detection_pilot.ops_log Could anyone help me on this? Thank you!Help to solve distinct value in month
Please help me some Power BI Experts, I have problem like the first picture when I extracted data. My table with the ID repeated the values each month Can anyone help me to write a DAX formula to Sum the distinct value group by ID and each Month like following table. Thank you very much.
What-if parameter : Visuals are not changing according to the parameter changes (using slicer)
I added a What-if parameter to my Power BI report and it generated a table and a measure. Using that measure I did a calculation as given below: Backup gen qty with powercut hrs = MainTable[Fuel per Hour] * 'power cut hours'[power cut hours Value] In here 'power cut hours'[power cut hours Value] is the measure created when adding What-if parameter. But the selected value from the slicer does not change the output in my above calculation, it just only give a blank and as a result visuals are not changing either. Please kindly give a solutionn for this.'Excess' Data when using YoY and QoQ measures
Hello, I am trying to get some analytical output for my report using QoQ and YoY measures in Power BI. But when put into a figure it displays data I do not need to show. My question is then, how can I limit the 'output' of my measures to fit what I need? QoQ Measure: Volume_In QoQ_Nominal = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_QUARTER = CALCULATE( SUM('DATA'[Volume_In]), DATEADD('Date'[Date].[Date], -1, QUARTER) ) RETURN __PREV_QUARTER ) YoY Measure: Volume_In YoY_Nominal = IF( ISFILTERED('Date'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE( SUM('DATA'[Volume_In]), DATEADD('Date'[Date].[Date], -4, QUARTER) ) RETURN __PREV_YEAR ) !To be noted: The Data I work with is within the period Q1 2019 to Q2 2021, and I am using a slicer to define the quarter I want to see, which can be seen to the right. Now, the problem is, that too much (and too little) data is shown in my figure (below): 1: The Chart is filtered using the beforementioned slicer. Though I would like it to show the QoQ and YoY values as they are available and will strengthen my output (this also applies for YoY values for all 2020 quarters in the figure). How can this be done? 2: I am only working with 'current' data in the sense that I only want to show actuals data from full quarters. When I choose to show data up until Q2 2021 in my slicer, I do not want the figure to show the YoY measure for Q3 and Q4. How can this be done? I hope I have explained my problem well enough for you guys to understand! Kind RegardsALLNOBLANKROW behaviour
Hi all, ALLNOBLANKROW isn't giving me the results I expect, i'm trying to understand why... My model is as follows: PROJECT BUDGET Project and Budget are related by ProjectCode (1 to many). Budget has a project (12340) which is not in related table Project. I have a measure: TotalBudgetNoBlanks = CALCULATE(SUM(Budget[Amount]), ALLNOBLANKROW(Budget[ProjectCode])) which gives me: I was expecting the blank row to be removed. When I add the project description to the table and modify the measure as such, it seems to do what I want: TotalBudgetNoBlanks = CALCULATE(SUM(Budget[Amount]), ALLNOBLANKROW(Project[ProjectCode])) Hoping someone could please explain why adding the description has this effect? I want to get blank row removed, using ALLNOBLANKROW and just the ProjectCode. Thanks in advance, Ben.
