Return value in matrix visual only for lowest level of hierarchy
I have a dataset that looks like this --> Category Brand SKU COGS Candy Bars Chocolate King 6oz $ 1.50 Candy Bars Chocolate King 10oz $ 2.00 Candy Bars Crunchie Bar 8oz $ 1.85 Gum Chewy Gum Fruit Flavored $ 0.50 Gum Chewy Gum Mint Flavored $ 0.65 When returning the value for Cogs, I only want to display the value for the SKU level. So in the matrix visual, right now it looks like this (using AVERAGE Dax formula) --> Instead I only want this --> Help?
Rolling sum in matrix categories with slicer
Hi, I'm quite new to DAX and Power BI and I'm having a struggle getting this rolling sum to work with category in matrix using a slicer in the report. We have a month slicer in the report and this sample is for 202111 selection. In matrix we want for each month to be shown rolling 12 month sum per areaid. I have managed to get the rolling sum to work without category, but it does not work with categories. Is this even possible with DAX? I know I could do this with other reporting tools so I would assume this should be possible with DAX too but I just can't figure it out. Here is the DAX I have now. ActionsRolling:= VAR endDate = MAX(Calendar[YYYYMM]) VAR startDate = endDate - 100 VAR RollingSum = CALCULATE(SUM(Actions[checkins]),FILTER(ALL(Actions), AND(Actions[YYYYMM] > startDate, Actions[YYYYMM] <= endDate))) RETURN RollingSum Actions_R12:= VAR endDate = MAX(TimeSlicer[YYYYMM]) VAR startDate = endDate - 100 VAR MeasureR = Actions[ActionsRolling] RETURN IF(MIN( Calendar[YYYYMM]) <= startDate, BLANK(), IF(MIN(Calendar[YYYYMM]) > endDate, BLANK(), MeasureR)) With this DAX we get the result like this. These numbers in matrix tables might not be 100% accurate as I quickly put them up in excel but you should get the idea of the problem here. 202012 202101 202102 202103 202104 202105 202106 202107 202108 202109 202110 202111 Area1 141 158 160 167 175 178 182 187 191 190 180 160 Area2 141 158 160 167 175 178 182 187 191 190 180 160 Area3 141 158 160 167 175 178 182 187 191 190 180 160 total 141 158 160 167 175 178 182 187 191 190 180 160 While the result should look like: 202012 202101 202102 202103 202104 202105 202106 202107 202108 202109 202110 202111 Area1 11 10 9 10 12 12 13 14 15 15 15 15 Area2 116 135 140 147 153 159 163 168 171 173 163 144 Area3 14 13 11 10 10 7 6 5 5 2 2 1 total 141 158 160 167 175 178 182 187 191 190 180 160 Here are the tables with joins. Thank you in advance! - Aku
Adjust matrix dates (in intervals) based on slicer selection
Hello, I have a Power BI report where I am using the Matrix Visual and have a date slicer. In the matrix, I have the Date value in the Column field. I need the dates to adjust with the dates selected in the slicer and also show in intervals of 7 days. I have tried binning the Date field into 7 days, but the last day shown in the Matrix is related to the latest Sunday, vs the last date selected in the slicer. I have also tried several dax formulas, but I can only get the last date shown in the matrix to correspond with the Maximum date in my data set. Any ideas on a formula that will allow me to show the Date field in the matrix in increments of 7 days and also display the last date that corresponds with the latest date selected in the slicer? (In my example below, the last date in the matrix should be 8/31 instead of 9/2). Here is the formula I am using to achieve this:Filtering "Red" and "Green" in a project with Measures
I have a project created with Measures. Can we filter red and green with measure in a matrix? To give more detail, i would like to filter the matrix in attached image by green or red. Ie. if i filter green, Region C1-Z August value shouldnt be visible. On the other hand, rest of the green values in August column should be visible. Another example ie. if i filter green Region C1-Y September value shouldnt be shown. But, rest of the green values in matrix including Region C1-Y August value should be visible. The rows containing any red value should not be deleted completely. I hope its clear. Looking forward for your suggestions.
Get difference in Matrix between two dates of a slicer (show zeros)
Hi everyone, I'm having problems creating a measure that can calculate the difference between 2 dates in a slicer, i created this one but doesn't seems to work like i would want: DELTA2 = VAR LASTDAY = [HOY] VAR PREVDAY = [ayer] VAR TOODAY = CALCULATE(SUM('DATA BASE'[Tons]),'DATA BASE'[FECHA DE CAPTURA] = LASTDAY,VALUES('DATA BASE'[OP])) VAR YESTERDAY = CALCULATE(SUM('DATA BASE'[Tons]),'DATA BASE'[FECHA DE CAPTURA] = PREVDAY,VALUES('DATA BASE'[OP])) RETURN TOODAY-YESTERDAY where: [HOY] = CALCULATE(MAX('DATA BASE'[FECHA DE CAPTURA]),ALL('DATA BASE')) and, [ayer] = CALCULATE(MAX('DATA BASE'[FECHA DE CAPTURA]) - 1,ALL('DATA BASE')) my result is this: it only kinda works with the last 2 dates, also i filtered the results of DELTA2 = 0, so it only displays the changes between days. I would like to reach a matrix like this (whatever date i choose, will be always one day and the next): if anyone can help me, i would really apreciatted.
Help with Matrix visualisation
Hi all, I am trying to create create a matrix visualization as below. Need to add two measures as columns in the Matrix. However, I am unable to drag and drop the measures as columns. I looked at similar topis in the forums, however, I am still struggling to do this. I appreciate any help.
Matrix visual tooltip to show values
I have a table of elements such as: Each person can have multiple categories. From this table I have managed to create a matrix like this: In order to achieve this (with help from this forum) I used the following Measure to aggregate the numbers: Measure = if(VALUES(Classification[Classification])=VALUES(Classification_dup[Classification]),0,CALCULATE([People],CALCULATETABLE(SUMMARIZE(VALUES(Data[IdPerson]),Data[IdPerson]),ALL(Classification),USERELATIONSHIP(Data[Classification],Classification_dup[Classification])))) I am now struggling with trying to create a tooltip that shows the Name (or ID) of the people in each cell, i.e. I want to create something that liiks like this: The mock up file is attached below: https://www.dropbox.com/s/i2wrw6zowwjzxs0/Cross%20matrix%20example%20post.pbix?dl=0 I would be very grateful for any help.Delete Matrix subtotal when i have 1 line with DAX
Hi guys! We have the following, we have 2 levels, one for country and another for cities. On the other hand, we have a measure that formats the constitution date. I put the screenshot to see the stage: I want to try to hide the constitution date for the country level. If you look at this I have achieved it when the country has 2 cities, but when I have only 1 city in that country it also shows it in the subtotal. For Example Spain has Jan.-20 however France is blank. The measures is the next one: Format Constitution Date = VAR firsconstitutiondate = LASTDATE(Data[Constitution Date]) VAR firsconstitutiondate_format = FORMAT( firsconstitutiondate, "mmm.-yy" ) var result = IF ( HASONEVALUE(Data[City]), firsconstitutiondate_format ) return result any suggestions? Thank in advance. Best Regards.
