Relationship status impacting data visualization?

[mrbrewmaster]

I apologize in advance; I am still very new to Power BI and the whole Power Platform.

I am using a custom order entity in Power Apps (CDS) to track order requests from customers. This order entity references three other entities via lookup fields. The first is to reference a current customer entity so I can attribute the order to them. The second is to reference a current brand entity of available brands. The third is to reference a current size entity of available sizes. A category quantity field for each Brand/Size combination. Finally, I have a calculated field to give me a subtotal for each combination of Brand/Size/Quantity. I have limited each order to one customer, and up to four combinations (for now).

I have imported this entity, and each of the reference entities into Power BI so I can start to visualize the order data. I have established relationships between:

• The customer entity (unique ID) and the customer field on the order.
• The size entity (unique ID) and the each of the four size fields (size1, size2, size3, size4) on the order.
• The brand entity (unique ID) and each of the four brand fields (brand1, brand2, brand3, brand4) on the order.

I am still learning the exact nature of the relationships.

Example:

Order 1- Customer A

Brand: A	Brand: B	Brand: C	Brand: A
Size: A	Size: A	Size: B	Size: B
Count: 10	Count: 2	Count: 10	Count: 10
Subtotal: $100	Subtotal: $20	Subtotal: $50	Subtotal: $50

Order 2- Customer B

Brand: D	Brand: A	Brand: B	null
Size: A	Size: A	Size: B	null
Count: 5	Count: 10	Count: 5	null
Subtotal: $50	Subtotal: $100	Subtotal: $25	null

When I visualize my data, I can get a total count of both orders, or total $ of both orders graphed by the Customer. I suspect it is because there is a single customer (and thus relationship between entities) per order. If I attempt (in a pie chart) to get a total count or total $ across all orders, with the Brand or Size as the legend, it will show the correct count or total $ but the chart will show those numbers split between each order’s Brand A (or size A). If I change which relationship is active the visualization will reflect that too.

Ideally, I want to be able to have a Pie Chart that shows total $ or count across all orders, with a date hierarchy, and the product mix graphed by brand with a size filter.

Any guidance is greatly appreciated! Thank you!

[mrbrewmaster · Accepted solution]

As I suspected the solution came down to my improper use of the Relations between entities. I spent some more time familiarizing myself with DAX. Specifically the USERELATIONSHIP function and managed to bypass the troublesome active relationship and specify a sales volume with a brand. 

Sorry for the confusing nature of my previous replies. I am still very new to this. I appreciate all the attempts to help clarify.

Total Volume (Filtered) = CALCULATE(SUM(volume1]), USERELATIONSHIP(brandid],brand1]))
+ CALCULATE(SUM(volume2]), USERELATIONSHIP(brandid],brand2]))
+ CALCULATE(SUM(volume3]), USERELATIONSHIP(brandid],brand3]))
+ CALCULATE(SUM(volume4]), USERELATIONSHIP(brandid],brand4]))

[Anonymous]

Hi @mrbrewmaster ,

Could you please provide your expected result in the form of table or screen shot? And what's the calculation logic of total $ or count? size1count+size2count+size3count+size4count? The information in this table which post in your latest post is a bit confusing, could you please explain the source of these data and its actual application scenario? It may need to make some adjustments according to your needs later...

In addition, you can refer the following blog to create the related pie chart.

How to Create a Power BI Pie Chart

Best Regards

Rena

[mrbrewmaster]

@Anonymous Apologies, the last post was a slightly redacted table as it exports from the entity in Power Apps. Below is a slightly easier to read example table (Ex.1) with similar content to my real table. I am utilizing Power Apps entities in CDS to track orders via a Model-based app. So every new submittal adds a row to the table.

Ex. 1

Ideally I would be able to take this data and transform it into a combination table like in the following example (Ex. 2).

Ex.2 

 

Then I could utilize the columns to make my pie chart as below (Ex.3a and 3b).

Ex. 3a

Ex.3b

And presumably filter in Power BI similarly to how I can in Excel (Ex. 4a and 4b)

Ex.4a

Ex.4b

Or combine separate volumes between the same brand to get a total volume per brand (Ex.5a and 5b)

Ex.5a

Ex.5b

In the actual instance. I have a measure created to calculate Total Volume. However when I apply the brand list in the Legend field it only labels the brands present in the first brand field from the entity (which is the active relationship, brand1 to brandlist). If I change which relationship is active, the brands shown will also change. In both cases the volume remains correct. It is only attributed to the active relationship brand.

 

I am also open to changing the way things are saved to the entity or adding fields as needed to preempt Power Query or Calculations in Power BI. 

I hope that all made sense. I am happy to continue to clarify as I am able (from an ability standpoint).

[mrbrewmaster]

To clarify, the data table in Power BI looks like this

Cust.

Brand 1

Brand 2

Brand 3

Brand 4

Brand 1 Size

Brand 2 Size

Brand 3 Size

Brand 4 Size

Brand 1 Count

Brand 2 Count

Brand 3 Count

Brand 4 Count

Customer A ID

A

B

C

A

A

A

B

B

10

2

10

10

Customer B ID

D

A

B

null

A

A

B

null

5

10

5

null

[amitchandak]

@mrbrewmaster , is your data is in the format you have mentioned in the first post and you want data like your second post ??

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[mrbrewmaster]

@amitchandakMy data looks like in my second post. Here is a table grab from my test.

distributionid

size1count

size1volume

size2count

size2volume

size3count

size3volume

size4count

size4volume

invoice_name

pickupdatetime

distribution_account

brand1

brand2

brand3

brand4

size1

size2

size3

size4

bd79c36c-66b5-ea11-a812-000d3a8a4934

1

0.5

2

0.32

2

1

2

1

TEST-06-23-20-1000

6/26/2020 17:00

c8f7cfe0-65b5-ea11-a812-000d3a8a4934

e0faa3ac-f299-ea11-a811-000d3a8a4934

e0faa3ac-f299-ea11-a811-000d3a8a4934

ada2a2e8-09a1-ea11-a812-000d3a8a4934

57e48445-85b2-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

9d35d35b-0fa1-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

fb63c6ef-c3b5-ea11-a812-000d3a8a4934

1

0.5

1

0.5

TEST-06-24-20-1004

7/10/2020 15:00

fc78dc14-2e9a-ea11-a811-000d3a8a4934

4258c728-f399-ea11-a811-000d3a8a4934

ee10d0d0-88b2-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

e1372cc3-c3b5-ea11-a812-000d3a8a4934

3

1.5

2

1

TEST-06-24-20-1003

8/3/2020 15:00

fc78dc14-2e9a-ea11-a811-000d3a8a4934

53cda586-9768-ea11-a811-000d3a8a4934

1838cc6e-f099-ea11-a811-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

11620893-c3b5-ea11-a812-000d3a8a4934

1

0.16

TEST-06-24-20-1002

8/5/2020 17:00

c8f7cfe0-65b5-ea11-a812-000d3a8a4934

4258c728-f399-ea11-a811-000d3a8a4934

9d35d35b-0fa1-ea11-a812-000d3a8a4934

16dc4bc9-c2b5-ea11-a812-000d3a8a4934

10

5

5

0.8

2

1

8

1.28

TEST-06-24-20-1001

7/1/2020 17:00

c8f7cfe0-65b5-ea11-a812-000d3a8a4934

64c19bbc-f299-ea11-a811-000d3a8a4934

ee10d0d0-88b2-ea11-a812-000d3a8a4934

ada2a2e8-09a1-ea11-a812-000d3a8a4934

7b55a5fd-09a1-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

9d35d35b-0fa1-ea11-a812-000d3a8a4934

e7d7c02a-0fa1-ea11-a812-000d3a8a4934

9d35d35b-0fa1-ea11-a812-000d3a8a4934