In a Matrix hierarchy, alter one node to a desired value
I have a matrix of sales in a hierarchy. For the SubCategory x, I want to correct the value 16 to a more up-to-date 24. So I want to normalize that node and all its children by a factor 24/16. I think I'm able to do that part. What I cannot figure out is adjusting the parent node and the total. Any help would be highly appreciated.
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?
Specific Filtering in a Gannt Chart Based Off Parent Level
I am wanting to be able to filter differently for each different Project, at the parent level. If I select Project1 in a slicer, I only want certain Task items to appear at the task level for that project in the Gantt Chart. I do not need all tasks to show up on the chart. For example, in Project1, there are Tasks 1-9. I only want Tasks 2,4,6 to show up on the chart. Now, if I would switch to Project2, I want the related task items to show up in the Gantt Chart based off the filter specific to Project2. For example, Project2 has Tasks 1-15. I want Tasks 1,3,6,7,8 to show up on the Gantt. The task level items for Project1 and Project2 are not the same and cannot have the same filters applied to them. I do not want the same task numbers to show up on the Gantt chart each time I switch to a different project. Is there a DAX code that will filter tasks based off the Project that has been selected? I was wondering if an IF statement or a SWITCH statement and paired with a FILTER statement, if any of those would work?
Hierarchy parent-child issue ( using only DAX )
hello everybody , I have these two tables in powerbi, my data arrangement is as showed in table 1 and in table 2 column ALL LEVELS exist , how can I add three columns to retrieve the values of each level in seperated column? I must do that with dax I have tried so many different way but still it does not bring the values in this order! I need more fresh ideas thank you in advance !Parent/Child Hierarchy - Show parent amount as its own row when filtered to next level
I'm working on a sales report that has 3 potential levels of a hierarchy. Sales can occur at all levels. Levels are Area Manager, Manager, Associate. I've built all the level functions into my table (flattened). I've built the paths and depths. I've read everything I can find, but I can't see to figure out how to show a sales amount for a "parent", without showing blanks for every level, and preferably I'd like to see the parent label repeated, instead of "blank". This is in Power Pivot - but it is the same problem in BI. The link is Example Workbook a sample of my problem. Annabel (an Area Manager) has 3 managers under her (and no associates under them). Bill (Area Manager) has 2 managers (Brad and Julie). Brad has 2 associates. Julie has none. Annabel has made some sales, and Brad has made some sales. Bill hasn't. Here is side by side of what I get on left that allows me to show Annabels individual contribution - forces every level to drill all the way to level 3, even though there's no child. I'd also like the parent level label to repeat. On the right is how I would like the table to look as I drill from 1 to 3. I've done several formulas where you can hide blanks if row depth is less than browse depth, but then I don't see Annabel's sales at all. The sum of the children doesn't equal the total for Annabel. Her amount is never displayed. This has been making me crazy! All ideas welcome. Thank you.Fix subtotal and total measure evaluated in differente levels of Hierarchy
Good morning I would really appreciate your help with this request: Context: I currently have a matrix within 3 levels in the rows and some measures Rows : Bank name (Category 1) Account Type (Category 2) Account Name ( Catgory 3) What I need is to create a measure who evaluates the account type taking into account there are 2 options, and return an specific calculation different for each one. This last request i just solved it using Measure 1. However, when I try to get the subtotals for Category 1 and the grand total, i just have not figured out how, because it must be sum of the results of evaluating the type of account, I mean the sum of the subtotals I got from Category 2 Table : Account Type cod name 1 COR 2 AHO For identify wich type an account is, I used the code from the table above Measure 1 : VAR COR= [Measure23]-[Measure44]+[Measure45] VAR AHO= [Measure22]-[Measure44]+[Measure45] VAR AT = SUM(Account_Type_code) RETURN SWITCH ( Account_Type, 1,COR,2,AHO,3,"") Here I need to get the right value that would be the sum of 26.498.449.893+7.430.738.545.94 , this per category and grand total. Thanks.
DAX path calculation
Hi, I am trying to calculate DAX path calculation and realized that it requires an additional column for hierarchy purposes. I however, have more than one manager that employees might need to report to in a building. Thus, I am trying to create a column that would bring in more rows for the same employee ID for the different manager ID. The image below is what it looks like now. And this is what I am trying to make it look like Is there a DAX calculation that would help me to make it look like it?Hierachrchy with additional values next to it.
Hey all, I am trying to create a status overview with an hierachy that should look the following (Used random values): My Excel looks like this: ID Name Value 1 a 1 1.1 aa 10 1.2 ab 11 2 b 3 2.1 ba 15 2.1.1 baa 20 3 c 25 Name: Value: a 1 aa 10 ab 11 b 3 ba 15 baa 20 c 25 I tried this using dax functions and building a hierarchy and then put it in a matrix and added the Value Colum. However my hierarchy looks like this: I already did plenty of research trying to solve this problem but could not find a solution. I tried it with one of Curbals-Videos with the ISBLANK function by creating the measure: Rem = SWITCH(TRUE(); AND(ISINSCOPE('Sheet1'[L2]);ISBLANK(VALUES(Sheet1[L2]))); BLANK(); AND(ISINSCOPE(Sheet1[L3]);ISBLANK(VALUES(Sheet1[L3]))); BLANK(); COUNTROWS(Sheet1)) ... but this does only work if I do not add another value to the matrix. Is there a way to solves this problem? Thank you very much! Philipp
