Need help measures in Matrix Visual
Experts, Need your help on the below secnario. 1. I have one measure and dates always fetches only two dates ex: June 2023 & Dec 2022. If I put my measure in matrix visual and keep the dates column in Matix columns and it gets splitted i.e. fine. However, Now Business user is asking to see the difference from current vs. previous only instead of Dec 2022. Below is snapshot current vs. expectation from business user. Current: Expectation:
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.Conditional formatting in matrix with text as values
Hello PBI community, I build a Matrix visualisation with text as values (using CONCATENAX Dax formula) and now I would like to do conditional formating on each line of my matrix. But it doesn't work for the moment as you can see on the screenshot, it only do it by "block" of values. Do you have any idea ? Thanks a lotFiltering "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.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
DAX Formula Not Correcly Applied as Filter to Matrix
Hi to all, I've a strange situation where a formula created to filter rows in matrix is not working While category must be 3 rows with category 2 are shown. My category formula is the following: MI_Categoria_CO = var fatt_0_12m = IF(ISBLANK(round([MI_Fatturato_0/12_Mesi], 2)), 0, round([MI_Fatturato_0/12_Mesi], 2)) var fatt_12_24m = IF(ISBLANK(round([MI_Fatturato_24/12_Mesi], 2)), 0, round([MI_Fatturato_24/12_Mesi], 2)) var fatt_24pm = IF(ISBLANK(round([MI_Fatturato_24p_Mesi], 2)), 0, round([MI_Fatturato_24p_Mesi], 2)) var fatt_36pm = IF(ISBLANK(round([MI_Fatturato_36+_Mesi], 2)), 0, round([MI_Fatturato_36+_Mesi], 2)) var pre_36m = IF(ISBLANK(round([MI_Fatturato_Pre36m], 2)), 0, round([MI_Fatturato_Pre36m], 2)) var categoria_fermo_oltre_2_anni = 4 var categoria_fermo_2_anni = 3 var categoria_fermo_1_anno = 2 var categoria_attivo = 1 var categoria = if(pre_36m <= 0 && fatt_36pm > 0, categoria_fermo_oltre_2_anni, if(fatt_0_12m + fatt_12_24m <= 0 && fatt_24pm > 0, categoria_fermo_2_anni, if(fatt_0_12m <= 0 && fatt_12_24m > 0, categoria_fermo_1_anno, if(fatt_0_12m > 0, categoria_attivo, 0)))) return categoria Is a simple formula to compare the results from other formulas. I paste the formula expression from one of them (there are built in the same fashion): MI_Fatturato_0/12_Mesi = var ultima_data_fattura = CALCULATE(MAX(Fatturato[DTFTST]),FILTER(ALLSELECTED(Fatturato), Fatturato[DTFTST] <> 0)) var data_come_data = LOOKUPVALUE(Calendario_Fattura_LUT[formato_data], Calendario_Fattura_LUT[DataKey], ultima_data_fattura) var anno = YEAR(data_come_data) var mese = MONTH(data_come_data) var giorno = DAY(data_come_data) var data_come_data_12mesi_prima = DATE(anno-1, mese, giorno) //var totale = CALCULATE([MI_Fatturato], FILTER(Fatturato, RELATED(Calendario_Fattura_LUT[formato_data]) >= data_come_data_12mesi_prima && RELATED//(Calendario_Fattura_LUT[formato_data]) <= data_come_data)) //return IF(ISBLANK(totale), 0, totale) var chiave_commerciale = IF(HASONEVALUE(Elenco_Ruoli_Attivi[chiave]), VALUES(Elenco_Ruoli_Attivi[chiave])) var codice_commerciale = LOOKUPVALUE(Elenco_Ruoli_Attivi[codice], Elenco_Ruoli_Attivi[chiave], chiave_commerciale) var totaleFatturato = IF(ISBLANK(codice_commerciale), round(CALCULATE(Misure[MI_Fatturato], FILTER(Fatturato, RELATED(Calendario_Fattura_LUT[formato_data]) >= data_come_data_12mesi_prima && RELATED(Calendario_Fattura_LUT[formato_data]) <= data_come_data)), 2), round(CALCULATE(Misure[MI_Fatturato], FILTER(Fatturato, RELATED(Calendario_Fattura_LUT[formato_data]) >= data_come_data_12mesi_prima && RELATED(Calendario_Fattura_LUT[formato_data]) <= data_come_data && ( RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O11]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O12]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O13]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O14]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O21]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O22]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O23]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O24]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O31]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O32]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O33]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O34]) = codice_commerciale || related(Anagrafica_Supplementare_Clienti_LUT[A26O41]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O42]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O43]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O44]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O51]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O52]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O53]) = codice_commerciale || RELATED(Anagrafica_Supplementare_Clienti_LUT[A26O54]) = codice_commerciale) ) ), 2)) return IF(ISBLANK(totaleFatturato), 0, totaleFatturato) The relevant tables used to built the matrix proposed before are a fact table and a related table to decode customers names as shown here: Hope to hear tips from you all, Gabriele
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?
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.
