Pivot table only showing variance between regions

[Mauram68]

Hello,

I have been stuck for some time now figuring this out. I have a table with regions and number of tickets. Now I want to create a pivot table which shows the regions in the rows and also in the columns. The value field should be populated with the difference of the number of tickets between the regions.  An example is shown in the table below.

Is this possible in Power BI? 

	North	South	West	East
North	0	-1	2	3
South	1	0	...	...
West	-2	...	0	...
East	-3	...	...	0

This is an example of the actual fact table:

Region	#Tickets
North	5
South	6
West	3
East	2

[123abc]

Yes, it is possible to achieve this in Power BI using DAX calculations and a matrix visual. You can create a calculated table or a DAX measure to calculate the variance between regions based on your ticket data.

Here's a general outline of the steps you can take:

1. Import your data: Import your fact table into Power BI containing the regions and the number of tickets.

2. Create a Calculated Table (optional): You can create a calculated table that represents the cross join of your regions. This will ensure that all combinations of regions are included in your analysis.

3. Create DAX Measure for Variance: Create a DAX measure that calculates the variance between regions based on the number of tickets. You can use DAX functions like SUMX and RELATED to calculate the variance.

4. Design your Matrix Visual: Place your regions in both rows and columns of a matrix visual. Then, place your DAX measure for variance in the values section of the matrix visual.

Here's an example of how you might write the DAX measure for the variance:

Variance =
VAR Region1 = SELECTEDVALUE('Regions'[Region1])
VAR Region2 = SELECTEDVALUE('Regions'[Region2])

RETURN
IF(
Region1 = Region2,
BLANK(), -- or 0 if you prefer
CALCULATE(
SUM('FactTable'[#Tickets]),
FILTER(
'FactTable',
'FactTable'[Region] = Region1
)
) - CALCULATE(
SUM('FactTable'[#Tickets]),
FILTER(
'FactTable',
'FactTable'[Region] = Region2
)
)
)

Ensure that your 'FactTable' and 'Regions' table names match your actual table names.

This measure calculates the difference in the number of tickets between two selected regions. If the regions are the same, it returns BLANK(). You can adjust this measure according to your specific requirements and the structure of your data model.

Once you have this set up, your matrix visual should display the variance between regions as per your requirements.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Mauram68 · Accepted solution]

Thanks. This was exactly what I was looking for.