Best choice of custom visual
Hello, I've been asked to prepare a visualisation in line with the following example: I'm somewhat stumped with regards to which custom visual (if any) to pick, and thought to branch out to the wisdom of the Power BI-community 🙂 Any and all assistance in this matter would be greatly appreciated! Best regards
I don't understand why a stock graph isn't categorizing by deposit
I have a Stock table called "S Stock" in which I have the column [tipodeposito] (which is the type of deposit) and [deposit] (which is the deposit per se). I created a Stock measure that only took into account some of the types of deposits and deposits. However, when I make a table in the report and categorize it by type of deposit, it shows the entire stock without differentiation. I have made this two ways (keep in mind [cantidad] in quantity): 1. Stock = CALCULATE(SUM('S Stock'[cantidad]), OR('S Stock'[tipodeposito] = "Consignado", OR( 'S Stock'[deposito] = "ALMACEN PYH", OR ('S Stock'[deposito] = "BODEGA PYH", 'S Stock'[deposito] = "Casa Big Sur")))) In this case, you should know all those examples of [deposito] are just a share of a [tipodeposito] that is called "Bodega" (which encompasses other deposits and doesn't intersect with "Consignado"). However, when I make the bar graph and try to differentiate the stock by [tipodeposito], this is how it appears: 2. In this case, I created two measures in order to better differentiate the stock I want to take into account from [tipodeposito] = "Consignado" and from [tipodeposito] = "Bodega". So: Stock Bodega = CALCULATE(SUM('S Stock'[cantidad]), OR('S Stock'[deposito] = "ALMACEN PYH", OR('S Stock'[deposito] = "BODEGA PYH", 'S Stock'[deposito] = "Casa Big Sur"))) Stock Consignado = CALCULATE(SUM('S Stock'[cantidad]), 'S Stock'[tipodeposito] IN { "Consignado" }) Later, I sum them up in the Stock measure: Stock = CALCULATE(Stock[Stock Bodega]) + CALCULATE(Stock[Stock Consignado]) And this is the graph I get (even weirder, as the "Bodega" has all the stock available and "Consignado" just a part of it, them being a simple column which categorizes all the stock in two categories): How can I have a graph in which the total stock is divided into these two categories, but in which "Bodega" only has those three deposits I mentiones ("ALMACEN PYH", "BODEGA PYH", "Casa Big Sur"), and without using filters in the table, just in the formula? Thanks!
Stop Line on graph from being continuous
I have a table where the costs go up to march and i want to put it on another graph that has a table that goes on until december. When i put it on the y axis, the line goes all the way through to decmeber, how can i get it to stop at march (the green and orange lines)?
Curve color depending on its position relative to another
I have 2 series of values: val1 and val2, and a column with dates. I want to show a graph with a date x-axis, and the 2 curves corresponding to val1 and val2. The 2nd curve should be red if below curve1 and green if above. The first solution I came with is to create 2 columns corresponding to val2 in my database: val2_up would have the same value as val2 if above val1, and would be NULL if below val1. Similarly, val2_down would have the same value as val2 if below val1, and would be NULL if above val1. The problem if I choose this solution is that I cannot add filters on family, category and type. Actually I could, but as PowerBI computes the total, If I choose to show for example only family1, it would compute 2 totals: the first with every values below val1 and the 2nd with every value above val1. Thus it would show 2 curves for val2, whereas I would only want 1: If you have any other idea, I would appreciate if you could share itHow to illustrate two groups of measures from one column in an overlay (picture encloses)
Hello, I have a requirement to show measure results depending on different temperatures (ambient temperature about 20 degree and heated temperature above 90 degree). There are several test rows showing the results depending on time and temperature. For the two temperatures areas (ambient and heated) I want to compare the results as show below, but with different colors per temperature area (ambient and heated) and not connect to eachother as show. My way to show was just limited. Sorry for that. The goal is t show that I can achieve a better result wih heated or ambient temperature. Here the data table Test-No. Temp (°C) Time/ sec Result 1 99,5 10 18 2 101 20 12 3 100,2 30 8 4 99,45 40 6 5 99,8 50 5 6 99,52 60 4 7 102 70 3,5 8 101 80 3 9 100,3 90 2,5 10 99,6 100 3 11 20 10 18 12 20,3 20 16 13 20,4 30 13 14 21 40 11 15 20,3 50 9 16 19,8 60 8 17 22 70 7 18 21 80 6 19 20,3 90 5 20 21 100 4 Thanks so much in advance. reg. KlausLegends in a graph
Hi there. I am currently comparing time periods graphically. However, when I put the agent or self-management legend to divide the bar in two as in the first image, it does not allow me. Is there any way I can put these two features on the same bar and not split it apart (like in chart two)?  Multiparent Graph, Recursiveness
Hi community, I am addressing the following issue, stemming from 2 observations concerning graph structures in DAX: The build in PATH function does not support several parents for one child. DAX is not recursive. This means that the implementation of graph exploration strategies starting with only e.g., an adjacency list is not possible in DAX My business problem is simple to understand: in a hierarchical company structure revenue of a child organization is recognized for several parents. A common application would be bonus agreements, where employees get bonus payments for revenues in child organizations. These bonus agreements cover then a completely internal structure which has nothing to do with the structure of the legal entities of the company, they rather are arbitrary. For these reasons I executed the entire graph search in Power Query and then handed over the result for visualization. The approach is outlined below. I have then several questions, where I would like your help: in the approach below, are there elements which can be replaced by features that are out of the box either in Power Query or DAX? If not, is there a pattern here, where necessarily recursive work is executed in Power Query and then handed over to the Vertipaq engine? Are there any performance concerns when doing so? Approach: We have as toy model a turnover table which specifies the turnover in the period and the child organizations. Organizations can have turnover or not (if they are pure umbrella organizations they do not). The real model has over 100 organizations and ideally, we explore the graph to get the dept of graph and do not put in this information beforehand. Therefore, the algorithm must be recursive. This is results in the following adjacency list: {{1,{3}},{2,{3}},{3,{4,5}},{4,{7}},{5,{}},{6,{4}},{7,{}}} Now some standard Depth First Search (DFS) is applied: DFS = (adjacencyList as list, visited as list, queue as list) => let result = if List.NonNullCount(queue) = 0 then visited else let v = Pop(queue){0}, restQueue = Pop(queue){1}, children = GetNeighbours(adjacencyList, v) in if List.Contains(visited, v) then @DFS(adjacencyList, visited, restQueue) else @DFS(adjacencyList, List.Combine ({ {v}, visited}), List.Combine({restQueue ,children})) in result All vertices are taken and the children are determined: GetDescendants = (adjacencyList as list, v as number) => let children = GetNeighbours(adjacencyList, v ), result = DFS(adjacencyList, {}, children) in result with GetNeighbours = (adjacencyList as list, v as number) => let selection = List.Select(adjacencyList, (x) => x{0} = v), result = if List.Count(selection) > 0 then selection{0}{1} else {} in result The output can be transformed into a table with the added feature that a vertex is its own child. let Vertices = Table.SelectColumns(Turnover, "Vertex"), Descendants = Table.AddColumn(Vertices, "Col1", each let v = [Vertex], children = GetNeighbours(TurnoverTree, [Vertex]), result = {v, GetDescendents(TurnoverTree, v)} in result), ls1 = Table.Column(Descendants, "Col1"), ls2 = List.Transform(ls1, (ls) => let v = ls{0}, n = List.Count(ls{1}), elementToAdd = if n > 0 then let generator = List.Repeat({v},n), zip = List.Zip({generator, ls{1}}) in zip else {} in elementToAdd), ls3 = List.Combine(ls2), ls4 = List.Transform(Table.Column(Vertices, "Vertex"), (x) => {x,x}), ls5 = List.Combine({ls3, ls4}), tab = #table( {"Vertex", "Child"}, ls5) in tab The rest is then straight forward. We can now assign now turnovers to the parents and have a completely basic unformatted visual like this (a nice visual is not the point of the question) Again, my key questions are: In the approach above, are there elements which can be replaced by features that are out of the box either in Power Query or DAX? If not, is there a pattern here, where necessarily recursive work is executed in Power Query and then handed over to the Vertipaq engine? Are there any performance concerns when doing so? Thank you for your help. Best regards ChristianArea graph -Power BI 101
What do I need to do in Power BI to show this table as an area graph with an X axis of date and Y axis of value, with each of the categories stacked in value. I want to be able to show total and drill down into each category if necessary. Darrin Category 01-Jan-22 01-Feb-22 ######## 01-Apr-22 01-May-22 01-Jun-22 01-Jul-22 01-Aug-22 01-Sep-22 01-Oct-22 01-Nov-22 01-Dec-22 01-Jan-23 01-Feb-23 01-Mar-23 01-Apr-23 01-May-23 Cat 1 £500,000 £569,722 £81,776 £113,583 £115,506 £153,297 £153,297 £170,743 £242,794 £230,740 £198,933 £198,933 £181,487 £111,765 £111,765 £111,765 £1,923 Cat 2 £0 £0 £0 £381,738 £524,656 £224,656 £224,656 £189,151 £189,151 £81,738 £81,738 £81,738 £0 £0 £0 £0 £0 Cat 3 £0 £0 £0 £0 £0 £103,691 £103,691 £103,691 £103,691 £103,691 £103,691 £103,691 £103,691 £0 £0 £0 £0
Diversity report - Using Measure in Legend of graph
I am trying to build a Diversity report for my organization. A simple example of the data I work with: Candidate IDGenderOrg lvl1Org lvl 2Org lvl2ShortlistedInterviewedOffered 1 Male Sport Squash Ball Yes Yes Yes 2 Female Sport Squash Ball Yes Yes Yes 3 Unknown Sport Squash Ball Yes Yes Yes I added an excel file with the above and a power PI report here: https://wetransfer.com/downloads/e15fd9ac80e4da54360195bf5c5552c420221125151306/8f48a8bb5a0a74c98be0f1c5aa6788f120221125151323/c20328 I need to show the diversity data for each characteristic Broken down for each recruitment step (shortlisted, Interview, Offer). As we cannot use measures in the x Axis of a graph I started by Adding a new column (column1) to use as reference: with each recruitment step. Using Dax I then created the below measure: Measure test = VAR Short = CALCULATE( SUM( Table1[Candidate Sum] ), Table1[Shortlisted] ="Yes") VAR Inte = CALCULATE( SUM( Table1[Candidate Sum]), Table1[Interviewed] ="Yes") VAR Offe = CALCULATE( SUM (Table1[Candidate Sum]), Table1[Offered] = "Yes") RETURN SWITCH( SELECTEDVALUE( 'Recrtuiment steps'[Column1]), "Shortlisting", Short, "Interview", Inte , "Offer", Offe ) This returns the Diversity data perfectly https://ibb.co/mtdDFtn Now my issue is that I need to stop the graph from showing the legend data (Gender) when the value is less than 5. The problem is that the legend cannot accept a value. Adding a if less than 5 works in removing the data: less than 5 test = VAR Short = CALCULATE( SUM( Table1[Candidate Sum] ), Table1[Shortlisted] ="Yes") VAR Inte = CALCULATE( SUM( Table1[Candidate Sum]), Table1[Interviewed] ="Yes") VAR Offe = CALCULATE( SUM (Table1[Candidate Sum]), Table1[Offered] = "Yes") RETURN SWITCH( SELECTEDVALUE( 'Recrtuiment steps'[Column1]), "Shortlisting", IF(Short < 5 ,Blank() , Short) , "Interview", IF(Inte <5 , Blank() , Inte) , "Offer", IF( Offe < 5 , Blank() , Offe ) ) This DAX will remove the data completely if less than 5. Good start but now it looks like we don't have any small numbers instead of hiding them. https://ibb.co/SBYQQ1w What I need is for the legend to be amended to merge/group categories that are less than into a new "Hidden" value. So instead of (picturing the graph): Non Binary 3% - 3 Unknown 2% - 2 Male 50% - 50 Female 45% -45 shortlist I currently get: Male 50% - 50 Female 47% -45 shortlist I need: Hidden 5% Male 50% - 50 Female 45% -45 shortlist Is this possible, or am I not using the correct approach? My set of raw data is bigger but I only selected this to understand the logic. Thank you
Lack of colours in dispersion graph (analicing differences)
Hello, In graph column using right bottom -> analyze decrease then click on dispersion graph. In desktop I can see the colours (red and green) Then I publish the report and in web versión the graph appears in white (without colours): I have tryed with differents browsers (FireFox, Chrome and Edge) and neither it works. Can you help me whith this issue? Regards.
