Question: I want PowerBi to report to me only the diversity (distinct) groups of each phase.
Question: I want PowerBi to report to me only the diversity (distinct) groups of each phase. Background: During my evaluation, I did a series of interviews over two phases (conduct and scoping) as show by the data table below. Problem: When I slice for 'conduct phase', I want it to report a list of only the DISTINCT/diversity of groups interviewed, which would be: LPL TYT GTG If I slice for 'scoping phase', I want it to show the DISTINCT/diversity of groups as: MRL GTG What I have tried: I have used the following DAX to create a second table (UniqueL1Table) of these distinct groups, and have it shown as a list, but it can only show as all diversity of both Conduct and Scoping groups....even if I slice for only conduct, it will still show as: LPL TYT GTG MRL UniqueL1Table = Distinct('Combined Tables'[L1]) Data used: Group Phase LPL Conduct Phase TYT Conduct Phase GTG Conduct Phase GTG Conduct Phase MRL Scoping Phase GTG Scoping Phase GTG Scoping Phase
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
Prior row value and Percentage change for non-sequential indexed rows
Dear all, I am new to using dax and was wondering if you could help. In the report below I was trying to calculate the % change where the report is from multiple table sources. The data is in a way so there are multiple dates from each underlying table, hence I just used the month number to remove the issue of showing multiple lines as the sale value is a calculation based on the totals for the month. In this, I was trying to calculate the % change based on the prior row. I have seen many examples of this where a clear date/month / year, is straightforward, although I was trying to do this based on the Line ref (as shown below). The calculations are as follows: LineRef = min ('Date'[Ref] ) Prior row value = CALCULATE ( [Net Sales], Dateadd( 'Date'[Date] , -1 , MONTH )) % Change = DIVIDE( ( [Net Sales] - [Prior row value]) , [Prior row value] ) RankRef = RANK.EQ ([LineRef] , 'Date'[Ref] , ASC ) Although I was struggling to get the Prior row value and % change to work due to the LineRef not being sequential, therefore I could not use current row less one etc to calculate the prior row. The issue is the multiple dates to use Dateadd . I also tried to use rankx to get a sequential number but that did not work. The idea being if Rankx worked I could pick up the prior row based on current rank less 1. I was wondering if there was a way to get the above to work, and needed your help with a solution that may work in this situation?
Return previous Month Balances from Running Data
I am trying to receive the last month's value for each row here. They all belong to the same item. However, I am only getting some previous monthly values in return, and I am not quite sure why. Here is the variable and DAX command used. Any answer to why only some are returning a value?Goal management assessment Power BI consolidation model
Good morning, I am working on a goal management dashboard in which some company goals are assessed by different people. There are three levels of goals, and the assessments must be consolidated from the lower level to the highest level. The L1 level contains all the goals completely consolidated. Every goal from L2 or L3 must have a L1 "parent" goal, in which will finally consolidate. The L2 level contains a disaggregation of certain L1 goals. It is a subgoal of the L1 level ones. The L3 level contains a disaggregation by country of either L1 or L2 goals. In the above graph each goal have a A or a C. A: Assessed goal. This goal is assessed by one or many people. Thus, it will appear in the input table. C: Consolidated goal. The goal is calculated from all the subgoals that are in lower levels. A C goal can be the calculation of some A goals and some C goals. Considering the 3 goals of the below image, I have created an adhoc assessment table for this example: Goal Value RA EUR 2 RA EUR 5 RA EUR 4 RA IND 7 RA IND 5 RB 2 RC.01 3 RC.01 1 RC.02 EUR 4 RC.02 EUR 3 RC.02 IND 2 RC.02 IND 6 RC.02 IND 4 RC.02 IND 7 RC.02 IND 4 RC.02 IND 5 RC.02 USA 2 RC.02 USA 1 In addition, each region has a weight for the calculation: Region Weight EUR 5 IND 3 USA 2 For example, the RA goal is calculated as shown below: The L3 is calculated with a normal average: RA EUR: (2 + 5 + 4) / 3 = 3,66 RA IND: (7 + 5) / 2 = 6 And it is consolidated in the L1 RA considering the weights: Region Weight % weight Assessment Weighted assessment EUR 5,00 0,63 3,66 2,29 IND 3,00 0,38 6,00 2,25 total 4,54 The RA goal has a 4,54 ponderation in total. The RB goal is a normal average of the assessments. The RC goal is calculated as shown below: Region Weight % weight Assessment Weighted assessment USA 2,00 0,20 1,50 0,30 EUR 5,00 0,50 3,50 1,75 IND 3,00 0,30 4,66 1,40 total 3,45 This is the final calculation schema: What I need is a report in which I can drill down from the L1 to the L3 obtaining details of the calcs. I have to come up with the model. Thus, I can implement almost any needed table. Does anyone know how to achieve this?Count of each month by year
I have two dates as created date and closed date. sample data as below, Created Date Closed Date 20 February 2019 2 March 2019 21 February 2019 10 March 2019 1 March 2020 4 April 2020 1 January 2021 2 February 2021 10 January 2021 23 February 2021 2 February 2021 5 March 2021 I calculated count of each month by year as below, Created Date Closed Date Created Date Count Closed Date Count 20 February 2019 2 March 2019 2 2 21 February 2019 10 March 2019 2 2 1 March 2020 4 April 2020 1 1 1 January 2021 2 February 2021 2 2 10 January 2021 23 February 2021 2 2 10 Februray 2021 23 March 2021 1 1 When I'm trying to create graph, I am passing Created Date and Closed Date in Axis and in Values i'm passing created date count, closed date count, but on graph not showing the correct count because i'm passing 2 different date's in Axis. I have tried using Calendar table also but while creating relationship it giving me an error: Circular dependancy was detected. Is there any other way to do it, without using UseRelationship Function.
