Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have looked at a couple of options to accomplish what I am looking for but not sure I follow the other solutions. Below is the layout I am looking for in my matrix. All I have is a single transaction table with all the sales and cost transactions (GL Ledger). Not ideal but out of my control.
I have created all the measures to calculate daily, current year, previous year and Month to Date sales and GP. However in order to place them in rows based on the type of measure they are eg. Sales, GP, or GP%, I need a way to classify or group the measures. Unlike segmentation where I could filter on a range of values without a relationship, my measure groups are arbitrary.
Hope this is enough information.
Peter
@Anonymous
Not very clear with text description, can you provide a sample table and an expected output matrix.
Regards
Paul
I posted a sample set of data. I have calculated measures table that uses this data to calculate Total_Daily_Sales, Total_COGS and Gross Profit. When added to a matrix with Division and Branch as rows and Date as Column everything looks good and calculates correctly for this year, month, Same day last year, previous year ect. The problem is with all the measures they don't cleanly fit on screen.
The layout I shared in the original post would move specific measures into additional rows. I have tried to add these additional rows to a config table but I haven't been able to get other DAX examples to work correctly.
The data really isn't the issue. My measures are Sales_MTD, Sales_YTD, GrossProfit$_Daily, GrossProfit%_Monthly etc
To get the Pivot I posted I need to label Sales calculations as sales, Gross Profit as GP, and Gross Profit % as GP%. The segmentation patterns seem to work with ranges.
Account | Division | Branch | Department | Account Type | Date | Amount |
7800 | MWC | GAD | Parts | Sales | 6/1/2020 | $1,000.00 |
5600 | MWC | GAD | Parts | COGS | 6/1/2020 | $500.00 |
7800 | MWC | GAD | Service | Sales | 7/1/2020 | $1,000.00 |
5600 | MWC | GAD | Service | COGS | 7/1/2020 | $500.00 |
7802 | MWC | GAD | NEW | Sales | 5/1/2020 | $55,000.00 |
7802 | MWC | GAD | NEW | Sales | 5/1/2019 | $12,000.00 |
5601 | MWC | GAD | USED | Cogs | 5/1/2019 | $35,000.00 |
5601 | MWC | GAD | USED | Cogs | 7/1/2020 | $23,000.00 |
@Anonymous , refer is these can help
column header grouping https://www.daxpatterns.com/dynamic-segmentation/
https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905
https://community.powerbi.com/t5/Desktop/grouping-measure-results/td-p/456795
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |