DAX to measure Same Store Sale
Hello everyone! I am having trouble writing a DAX formula in Power BI to calculate Same Store Sales (SSS). My calculation of SSS is (CURRENT_VALUE_ / PREVIOUS_VALUE_) - 1. The problem is that, when looking at the CLIENT_ID hierarchy in a matrix visual in Power BI, I want the SSS measure to consider only the CURRENT_VALUE_ of the brands that also had sales in PREVIOUS_VALUE_. For example, in the figure below, I want CLIENT_ID 123456 to show me an SSS of 27% in total, instead of 100%. That is, I want it to consider (140 / 110) - 1, instead of (220 / 110) - 1, as it should only sum the CURRENT_VALUE_ of the brands that also have PREVIOUS_VALUE_ (that is, only the GREEN and PINK brands). My current DAX formula is as follows: VAR tempTable = SUMMARIZE(clients, clients[CLIENT_ID], "PER_ATUAL", [CURRENT_VALUE_] ,"PER_ANTER", [PREVIOUS_VALUE_]) VAR currentValue = SUMX(FILTER(tempTable, NOT ISBLANK([PER_ATUAL]) && NOT ISBLANK([PER_ANTER])), [PER_ATUAL]) VAR previousValue = SUMX(FILTER(tempTable, NOT ISBLANK([PER_ATUAL]) && NOT ISBLANK([PER_ANTER])), [PER_ANTER]) RETURN IF(DIVIDE(currentValue, previousValue, BLANK()) -1 = -1 , BLANK(), DIVIDE(currentValue, previousValue, BLANK()) -1 ) Any help or tips?
How to aggregate in a measure without actually aggregating?
HI! so I am trying to use the isinscopre function in a matrix to show different transaction count values based on the hierarchy of Event Name -> Offer ID -> SKU. My measure works fine to switch teh values betweek sku and offer, but for osme reason it's showing the total sum of ALL events and not just the associated event. I'm wondering if it's something wrong with my model? Below is the measure TY Transactions = VAR sku = SUM('Consolidated SKU Sales'[Total transactions]) VAR offer = SUM('Consolidated Offer'[ty trans count]) VAR event = SUM('Consolidated Events'[ty trans count]) RETURN IF( ISINSCOPE('Consolidated SKU Sales'[SKU]), sku, IF( ISINSCOPE('Consolidated SKU Sales'[Offer Name Tx]), offer, IF( ISINSCOPE('Consolidated SKU Sales'[Event Nm]), event ) ) ) So from left to right, this is my transactions measure, the count for the event, the count for teh offers for that event (one offer in this example), and the count for differnet sku products. Looking across the rows, it works fine except for that event, which is showing the sum of total counts for all. The event, sku,and offer tables are connected with unique keys, and there are unique values of the event id, so i'm not sure why this isn't calulating correctly. Is there a way to just not use Sum at all in my measure, and have it display the correct associated count? Thanks!
Filter Matrix visual based on the current level In Scope, without affecting not in Scope values.
Hi All, I'm trying to filter a Matrix visual based on a measure value category (segmenting the measure values). the problem is that the filter is filtering all rows in the visual, while the desired result would be just filtering the rows in the current context (i.e. based on the currently visible hierarchy of the matrix). For Example, the delta% value of OFQ subsystem was 60.6% before filtering, and when a filter was applied the value changed to 79.7%. what is happening is that the filter is filtering out the rows in the next level ([Area] in this case); thus the value changes accordingly. The desired output is to keep the 60.6% value as it's. and this should by dynamic based on the currently displayed level. After a long research and trials, I've tried to detect the current displayed level using ISINSCOPE/HASONEVALUE functions, then exclude next level values with ALLSELECTED/REMOVEFILTERS. but it doesn't seem to detect the current level in scope correctly. Hope you can help me here. Please note that I can't create the category filter in a column since all of my values are based on slicers selections; thus, I can't summarize the data in a table or a column. that's why I had to use a measure and add it to visual filters. here is a sample report that mimics what I'm trying to do: question.pbix Thanks a lot,,
Matrix Values Reflecting Blank Instead of 0
I have a Power BI Matrix with Rows comprised of PHA_CD_NM and the DVLPT_NUM within each PHA_CD_NM. I have three separate measures I’ve included as columns within the Matrix. The problem is that Average Vacant Days (DDA) and Average Vacant Days (Non DDA) are calculating as blank values instead of 0 (when applicable). Can anyone help me adjust these formulas so blank items appear as 0? The formulas for each measure are below. Average Vacant Days = VAR AvgVacDays = DIVIDE(SUM('F_T_UNIT'[VAC_DAYS]), SUM('F_T_UNIT'[UNIT_CNT])) VAR IsBlankAvgVacDays = ISBLANK(AvgVacDays) RETURN IF( IsBlankAvgVacDays, IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDays ), BLANK() ), AvgVacDays ) Average Vacant Days (DDA) = VAR AvgVacDaysDDA = CALCULATE( [Average Vacant Days], 'F_T_UNIT'[UNIT_DDAPP_INDR] = "Y" ) VAR IsBlankAvgVacDaysDDA = ISBLANK(AvgVacDaysDDA) RETURN IF( IsBlankAvgVacDaysDDA, IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDaysDDA ), BLANK() ), AvgVacDaysDDA ) Average Vacant Days (Non DDA) = VAR AvgVacDaysNonDDA = CALCULATE( [Average Vacant Days], 'F_T_UNIT'[UNIT_DDAPP_INDR] <> "Y" ) VAR IsBlankAvgVacDaysNonDDA = ISBLANK(AvgVacDaysNonDDA) RETURN IF( IsBlankAvgVacDaysNonDDA || AvgVacDaysNonDDA = BLANK(), IF( ISINSCOPE('D_PHA'[PHA_CD_NM]), IF( NOT ISINSCOPE('D_DEVELOPMENTS'[DVLPT_NUM]), 0, AvgVacDaysNonDDA ), 0 ), AvgVacDaysNonDDA )Conditional formatting via isinscope()
Hi All, Via a measure and the function isinscope() I assigned conditinal formatting on 3 different hierarchical levels (matrix rows). This working just fine, but the business wants to have a button where they can filter based on the numbers which are red. The problem is now that when the third hierarchical level is red a filter on the color code does not return all 3 hierarchical levels. Is there a function where you can overwrite these levels? So you can say that when isinscope(Level 2) level 1 is returend, but also level 3. Measure conditonal formatting: IF (ISINSCOPE(hierarchical [level 3]),blank(), IF (ISINSCOPE(hierarchical [level 2])&&([Difference %])>varLevel2%),"#ff2000", IF (ISINSCOPE(hierarchical [level 1])&&([Difference %])>varLevel1%),"#ff2000"))) The sample PBIX is on this wetransfer link: https://we.tl/t-9TvD36eFPP
Switch, True & ISINSCOPE
Hi Everyone! I have the following measure, which depending on what level of the organisation is selected in the slicer will show on a clustered column and line visual either the Headcount Budget or blank/nothing. The reason being is that we only had budget set at domain level, and i wanted to ensure when someone selects below this level they would see no data for headcount budget. This has now changed and for cc_domain = "non-domain" we have headcount budgets set for the obs_level3, what I cant work out is how to change the code below to show headcount budget at obs_level3 where cc_domain = "non-domain", but not where cc_domain <> "non-domain" Headcount_Budget_Wkly_Snap = VAR HC = "" VAR HCB = CALCULATE([Headcount Budget]) VAR obs_l7 = ISINSCOPE(OBS_MAPPING[obs_level7]) VAR obs_l6 = ISINSCOPE(OBS_MAPPING[obs_level6]) VAR obs_l5 = ISINSCOPE(OBS_MAPPING[obs_level5]) VAR obs_l4 = ISINSCOPE(OBS_MAPPING[obs_level4]) VAR obs_l3 = ISINSCOPE(OBS_MAPPING[obs_level3]) VAR domain_sel = ISINSCOPE ( CC_OWNERS_LN[cc_domain] ) RETURN SWITCH ( TRUE (), obs_l7, HC, obs_l6, HC, obs_l5, HC, obs_l4, HCA, obs_l3, HCB, domain_sel, HCB, HC ) Any thoughts, is it even still possible to use a varient of the above code for this new scenario? Cheers Andy
