Forum Discussion
Aggregate Revenues based on Gross Margin % using DAX/Data modeling
Hi everyone,
This is my first question on PBI community. I am not new to using PBI but I need your help to get this visual right.
The end result should look like the image. The measures are simple SUM and DIVIDE for Remaining Revenue and Gross Margin %. I want to know how to aggregate the Remaining Revenue by Gross Margin % in to these buckets? The data model is a date dimension table connected to the Work In Progress 'WIP' fact table through dateid.
Thanks in advance for your help!
4 Replies
Hi Anonymous,
I think you need to create a table with margin ranges(category, start, and end), then you can create a visual with margin category and write a measure to calculate based on the current margin category range limit.
If you confused about coding formula, please share some dummy data to test:
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
- amitchandak
Super User
Anonymous , refer
SEGMENTATION
https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualizationamitchandak - Thanks for your quick response to this post. These links are great resources. I will go through them and get back to this thread.
I ended up using a summarized table for this solution such as this. I had to group them based on Sub Regions and MonthYear fields. Did the math on the measures. Created a relationship of many to many with the date dimension on MonthYear to be able to filter on the report.
SummarizedRR =
SUMMARIZECOLUMNS (
d_Location[Region],
d_Location[Sub Region],
d_Date[Year],
d_Date[MonthYear],
"Total Remaining Revenue", SUM ( f_WIP[Remaining Revenue] ),
"Total Remaining Revenue Short", SUM ( f_WIP[Remaining Revenue] ) / 1000000,
"Total Remaining Profit", SUM ( f_WIP[Remaining Profit] ),
"GM%", DIVIDE ( SUM ( f_WIP[Remaining Profit] ), SUM ( f_WIP[Remaining Revenue] ), 0 )
)
