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"?
Max function showing wrong. Need help for Powerbi Matrix total
Hello everyone, I need your support to solve and understand issues in my DAX. I have a transactionDB of all bank transaction like below: date company type Source Bank amount 01-Apr-22 Company 1 Credit Bank Bank A 10000 01-Apr-22 Company 1 Credit Bank Bank B 10000 01-Apr-22 Company 1 Debit Bank Bank B 500 01-Apr-22 Company 2 Credit Bank Bank C 10000 01-Apr-22 Company 2 Credit Bank Bank D 10000 01-Apr-22 Company 2 Debit Bank Bank C 10000 02-Apr-22 Company 1 Debit Bank Bank A 3000 02-Apr-22 Company 1 Debit Bank Bank B 1000 03-Jun-22 Company 2 Credit Bank Bank D 30000 03-Apr-22 Company 1 Credit Bank Bank A 5000 03-Apr-22 Company 2 Credit Bank Bank C 60000 03-Apr-22 Company1 Debit Bank Bank A 2000 03-Apr-22 Company 2 Debit Bank Bank C 10000 03-Apr-22 Company 2 Debit Bank Bank D I want to build a Dashboard with Matrix like below. Users wants to see today (slider date 3/06/2022) 1. Opening balance of 03/06/2022 (sum of all debit - credit as of of 2/06/2022) 2. Sum of credit happened on 03/06/2022 3. Sum of debit happened on 03/06/2022 4. Closing balance of 03/06/2022 If the user changes the date slider to 2/06/202, they will see above status as of 02/06/2022 Solution 1. Opening Measure (This is working fine with slider and all values) CALCULATE( SUM(transactionDB[Amount]), FILTER(ALLSELECTED(transactionDB[date]), ISONORAFTER(transactionDB[date],MAX(transactionDB[date])-1,DESC))) 2. Credit (Matrix is showing wrong calculation) CALCULATE( SUM(transactionDB[credit]),FILTER(ALLSELECTED(transactionDB[date]),transactionDB[date] = MAX( transactionDB[date]))) I want to show the credit happened in Slider date ie. 03/06/2022. It is not working as expected. 3. Debit(Matrix is showing for wrong calculation) CALCULATE( SUM(transactionDB[debit]),FILTER(ALLSELECTED(transactionDB[date]),transactionDB[date] = MAX( transactionDB[date]))) I want to show the debit happened in Slider date ie. 03/06/2022. It is not working as expected. 4. Ending balance (This is working fine with slider and all values) CALCULATE( SUM(transactionDB[Amount]), FILTER( ALLSELECTED(transactionDB[date]), ISONORAFTER(transactionDB[date], MAX(transactionDB[date]), DESC) ) ) Please help to solve this issue. I understand that using max function will summarize the value based on maximum date filtered within its context. But i would like to show the debit or credit happened for that particular date as per date slider ChandeepChhabra GuyInACube johnt75 tamerj1
Matrix Grouped Column issue: Columns are appearing with Gap in between.
Hi, I'm working on a Power BI paginated report. I have a Matrix with a column group. This Matrix has three parent groups. Grouped columns are dynamic as its number of columns can increase or decrease based on its parent groups. This metrix is to be replicated for multiple times based on the iteams selected in the parameters. Now because based on parent groups all columns will not be a part of each replication on matrix. Example: Prt Group1 -- Prt group2-- Prt group3--Matrix with 3 columns, Prt Group1 -- Prt group2-- Prt group3--Matrix with 6 columns, Prt Group1 -- Prt group2-- Prt group3-- Matrix with 4 columns Now when data is displayed in report Columns are not staring from the 1st place from where column1 should started instead, if it is on the 5th place or as per the auto sort; is starting from nth place having first few column space as blank. These few blank space are left because in same report's another set of matrix replication will have those few columns which are not part of first matrix replcation. Similarly in each set of data displayed in the matrix columns are getting spread out. Ex: Parent group 1 Parent group 2 Parent group 3 (Set 1) Metrix Data: Gap Gap Column1 Column2 Column3 Column4 Column5 data data data data data Parent group 1 Parent group 2 Parent group 3 (Set 2) Metrix Data: Column1 Column2 Gap Gap Gap Column3 Column4 Column5 Column6 Column7 data data data data data data data I would like to know how to start each column from its first place in each set with out any gap. Please suggest can this be achieved in Power BI paginated report? Thank you for time.
