Forum Discussion
Relationship status impacting data visualization?
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!
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]))
6 Replies
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
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.
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
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
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).
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]))
