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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
18 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |