Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I want to create a report which allows the user to select some group from within the dataset and then a subgroup from within that group and use aggregations of the group and subgroup to create measures
for example:
If the data is fresh produce, then the user can select "fruit" as the group and "oranges" as the subgroup and then it might calculate (sales of oranges )/ (sales of fruit); the percentage of fruit sales which were oranges
or they could pick: "fruit and vegetables" as the group and "oranges" as the subgroup
or "fruit and vegetables" as the group and "fruit" as the subgroup. In each example I want to have returned the subgroup as a percentage of the group.
I thought it was important that the user can only select subgroups which are contained within the group so I set up relationships as follows:
This allows the slicers in the report to function sufficiently close to how I want.
Now I want to create measures such as:
This makes sense, in the case where it fails the filters from the subgroup are still applying while in the case where it works they don't.
I assume that it doesn't work because USERELATIONSHIP is disabling the relationships in a way which matches the fail case.
Thanks.
Solved! Go to Solution.
@TheLoonies
Please try
NumberPOsMacro =
CALCULATE (
DISTINCTCOUNT ( PurchaseOrder_ProductEntity[PurchaseOrderID] ),
CROSSFILTER ( 'Entity Macro'[EntityID], 'Entity Micro'[EntityID], NONE ),
USERELATIONSHIP ( 'Product_Entity'[EntityID], 'Entity Macro'[EntityID] )
)
@TheLoonies
Please try
NumberPOsMacro =
CALCULATE (
DISTINCTCOUNT ( PurchaseOrder_ProductEntity[PurchaseOrderID] ),
CROSSFILTER ( 'Entity Macro'[EntityID], 'Entity Micro'[EntityID], NONE ),
USERELATIONSHIP ( 'Product_Entity'[EntityID], 'Entity Macro'[EntityID] )
)
this didn't work, but
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
7 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |