Want to create a custom table within query

[thecorporateguy]

Hi Everyone,

I need your help in create a custom table from my existing Power BI model. Basically, I have a master table called "Combined Data' which has all the sales data of the company e.g. Opportunity Details, Forecast Category, Total Value etc. Total there are 30 columns in this table. My main requirement is to create a visual which shows Sales based on Forecast Category (sounds simple), however, the catch is that the groups are kind of overlapped within this column. e.g. Forecast Category has 4 types of values

Forecast Category

Booked

Risk

Upside

Secure

I want to show a visual (Bar chart preferred) which shows total sales by 1. Forecast (Booked+Secure+Risk) 2. Risk Only 3. Secure Only. The challenge is to show the 3 bars together without drill down. Since "Forecast" group is overlapping with "Risk", i cannot just create 2 columns using If statement to mark the rows with the correct label as this will enable the "drill down" option when stacking category in "Y-Axis".

I then tried to create a custom grouping which will make it easier for me to group the values, However, I am not able to figure out the correct Dax or approch to achieve that (example below):

Category	Group
Forecast	Booked
Forecast	Secure
Forecast	Risk
Risk	Booked+Secure
Risk	Risk
Upside	Booked+Secure
Upside	Upside

Please help me to understand how can i approach this problem and potentials solutions to create the required result.

Thank you.

[Anonymous · Accepted solution]

Hi @thecorporateguy 

You can create three measures to calculate the total sales of three groups individually, then add all three measures to Y-Axis. In this scenario, you cannot add a field to Legend. 

Booked+Secure+Risk =
FILTER (
    'Combined Data',
    'Combined Data'[Forecast Category] IN { "Booked", "Secure", "Risk" }
)

Booked+Secure+Risk =
FILTER ( 'Combined Data', 'Combined Data'[Forecast Category] = "Risk" )

Booked+Secure+Risk =
FILTER ( 'Combined Data', 'Combined Data'[Forecast Category] = "Secure" )

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[thecorporateguy]

Can anyone please support?