Forum Discussion
How to group/classify measures segmentation
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
4 Replies
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/456795The 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 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.
