Forum Discussion
Helper IVCalculate segment
Hi
I have table 1 and wanna create the output table in power BI with below logic:
Calculate the No. of SO that have the "Discount/Quantity" belong to 3 segments :" 0 -2, 2-6 and >= 6"
Discount/Quantity of 1 SO = Total discount of this SO / Total Quantity of this SO .
ex : Discount/Quanty of SO "A" = (60+30)/ (10+12 ) = 4.1
Is there anyway to get the output without using calculated column?
I have
HI ngocnguyen,
You can create a table with all segment types and use it with the region to create a matrix.
After these, you can write a measure with summarize function to calculate the aggregation based on 'Region' and 'SO', then you can use get the current region and segment category to filter on the variable summarize table records.In addition, you can also take a look at the following blog if helps:
Dynamic segmentation – DAX Patterns
Regards,
Xiaoxin Sheng
4 Replies
- lbendlin
Super User
Not sure what "Is there anyway to get the output without using calculated column? " means - do you want measures, or do you want a Power Query based solution? Please clarify.
ngocnguyenlbendlin
I want to use Measure. Could you please help?- lbendlin
First create a list of your buckets. Then do a crossjoin with the region values, and finally compute the measure for each combination.
HI ngocnguyen,
You can create a table with all segment types and use it with the region to create a matrix.
After these, you can write a measure with summarize function to calculate the aggregation based on 'Region' and 'SO', then you can use get the current region and segment category to filter on the variable summarize table records.In addition, you can also take a look at the following blog if helps:
Dynamic segmentation – DAX Patterns
Regards,
Xiaoxin Sheng
