Pivot with multiple tables / Waterfall chart

[ww2020]

I have a number of questions when I put Power BI in use of my daily business. Luckily, most could be addressed in the previous community discussions. However, I still got the remaining issues as below.

Case 1:

I want to create a waterfall chart for each segment to illustrate the movements for the budget, variance (actual-budget) and actual.

For example, the expected chart for segment A:

Customer	Actual	Budget	Difference
Customer A	10	100	-90
Customer B	20	100	-80
Total	30	200

Green is Budget, Red is variance, Blue is Actual

Raw data:

Segment	Customer	Sales	Period
Segment A	Customer A	10	Actual
Segment A	Customer B	20	Actual
Segment B	Customer C	20	Actual
Segment B	Customer D	30	Actual
Segment C	Customer B	30	Actual
Segment C	Customer C	40	Actual
Segment D	Customer D	40	Actual
Segment D	Customer A	50	Actual
Segment A	Customer A	100	Budget
Segment A	Customer B	100	Budget
Segment B	Customer C	50	Budget
Segment B	Customer D	50	Budget
Segment C	Customer B	25	Budget
Segment C	Customer C	25	Budget
Segment D	Customer D	200	Budget
Segment D	Customer A	200	Budget

In query editor, I use 'Pivot Column' and add a column to sort as below:

= Table.AddColumn(#"Pivoted Column", "Difference", each [Actual]-[Budget])

Segment	Customer	Actual	Budget	Difference
Segment A	Customer A	10	100	-90
Segment A	Customer B	20	100	-80
Segment B	Customer C	20	50	-30
Segment B	Customer D	30	50	-20
Segment C	Customer B	30	25	5
Segment C	Customer C	40	25	15
Segment D	Customer A	50	200	-150
Segment D	Customer D	40	200	-160

**Questions:

But then how can I add up sub-total for each segment's budget and actual total and store under column 'Difference' so that I can create a waterfall chart with category 'Customer' and Y axis 'Difference' with filter 'Segment'? What is the quickest way to show the four charts?

**Questions:

Many posts have already mentioned the needs of starting value (e.g. Budget) and ending value (e.g. Actual) in the waterfall chart, but not the fixed name 'Total' shown always in the chart. However, it seems currently no free way/feature to get this in MS Power BI. Please correct me if I am wrong.

Case 2:

Table 'Actual' :

Category	Actual	Region	Segment
Category A	10	I	Segment A
Category A	20	II	Segment A
Category B	30	I	Segment B
Category C	30	II	Segment B
Category D	40	I	Segment C
Category D	50	II	Segment C

and Table 'Budget':

Category	Budget	Region	Segment
Category A	100	I	Segment A
Category A	100	II	Segment A
Category B	50	III	Segment B
Category B	50	I	Segment B
Category C	25	II	Segment C
Category C	25	III	Segment C
Category D	200	I	Segment D
Category D	200	II	Segment D

Table visualization below works If I match the two tables column[Segment] into the relationship (many-to-many).

**Question: How to correct the total amount under category [Budget] as 700 includes Segment D

However, if the relationship changes: Link Region to Region (many-to-many), then the table cannot function normally.

But if I change from Region to region, then it works.

**Question:

Without unique identifier, the visualisation is easy to generate wrong figures, especially when I need to input a number of tables (and most belong to many-to-many relationships). However, in MS excel I can simply use sumif function to get the figures from different excel tabs. Do I miss anything? How can I solve it without creating any unique identifier?

[v-lid-msft]

Hi @ww2020 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @ww2020 ,

Case1, For the first question, we cannot create four charts by one step, we can duplicate one into four and change the visual filter Segment to differnt value of them.

For the second question, we did not understand your requirement clearly, do you want that the blue total bar always show in the chart?

Case2, We suggest to create a dim table such as following :

Segment = Distinct(Union(Distinct('Budget'[Segment]),Distinct('Actual'[Segment])))

Regions = Distinct(Union(Distinct('Budget'[Region]),Distinct('Actual'[Region])))

Then we can create relationship between the dim table and fact table with one to many.

But if you do not want to create a table with unique value, we can also use the measure to meet your requirement:

Actual Measure =
CALCULATE (
    SUM ( 'Actual'[Actual] ),
    FILTER (
        ALLSELECTED ( 'Actual' ),
        'Actual'[Segment] IN FILTERS ( 'Actual'[Segment] )
            && 'Actual'[Segment] IN FILTERS ( 'Budget'[Segment] )
            && 'Actual'[Region] IN FILTERS ( 'Actual'[Region] )
            && 'Actual'[Region] IN FILTERS ( 'Budget'[Region] )
    )
)

Budget Measure =
CALCULATE (
    SUM ( 'Budget'[Budget] ),
    FILTER (
        ALLSELECTED ( 'Budget' ),
        'Budget'[Segment] IN FILTERS ( 'Actual'[Segment] )
            && 'Budget'[Segment] IN FILTERS ( 'Budget'[Segment] )
            && 'Budget'[Region] IN FILTERS ( 'Actual'[Region] )
            && 'Budget'[Region] IN FILTERS ( 'Budget'[Region] )
    )
)

Best regards,