How do I group columns by category in the same pivot table in excel?
Good afternoon guys, what's up? I need some help I'd like to layout the PowerBi "Matrix" in the same way that the "Pivot Table" groups the categories before showing the value, as shown in the photos below. 1. Excel - Pivot Table In the image below we can see that the pivot table first categorizes the values - Volume/Weight and Volume Measure - before grouping by BAT/PMI and JTI 2 - Power BI - Matrix In Power BI, the "Matrix" does just the opposite, first it categorizes BAT/PMI/JTI and then it categorizes the values I would like to know if it is possible to visualize the "Pivot Table" (example of photo 1) in PowerBI with "Matrix"?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.
Re-order column on matrix visualization
Hi, I have created matrix visualization like below. But, I need to move the latest (W-1,W-2) under each of Freq and SLF. Also I need to make it W-1 first and then W-2. Here I attached an example for what visualization I want. Could anyone help me on this case please? Thanks in advance 🙂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
How to create power BI visual for matrix table from multiple sheets.
Im new to POWER BI. How to create visual for matrox table? I need to create a dashboard with two bar charts, the first of which will display reports by category. Second, the chart will display the product-based report. However, in order to create an interactive main chart, I need to connect two sheets. If you click the corresponding category in the main chart, the second chart will be based on the first chart's selected category. How to do that?
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
