Forum Discussion
Calculate average percentage value and multiply by value through bridge table
- 5 years ago
Without data monkeymagik it is hard to adequately help, but let me offer a few things:
- You said it breaks if you use software description. I don't see that in your model so I don't know what you are meaning here. I will use the PCT Price List[Description] field though in my example below.
- Be mindful of the way your relationships are going. Your model looks good, but you cannot just do normal aggregation or itteration measures (SUM, SUMX, etc) between these tables. Data in [User Count] does not filter [License Groups] for example. It goes the otherway. [License Group Mapping] doesn't filter [License Groups] either.
You need to turn on bi-directional filtering in your measure. so, if you dropped the description field in your matrix, to get to data in the [User Count] table to work, you need to enable bi-di filtering between [License Group Mapping] and [License Group]
Test measure = CALCULATE( SUMX( UserCount, UserCount[Value] ), CROSSFILTER ( LicenseGroups[License Group], LicenseGroupMapping[License Group], BOTH ) )the CROSSFILTER function within CALCULATE will change filter directions to ONE, BOTH, or NONE as desired. BOTH is the most common use of this.
Once you do that, you will have a path from the PCT Price List table all the way to the user Count table.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
Without data monkeymagik it is hard to adequately help, but let me offer a few things:
- You said it breaks if you use software description. I don't see that in your model so I don't know what you are meaning here. I will use the PCT Price List[Description] field though in my example below.
- Be mindful of the way your relationships are going. Your model looks good, but you cannot just do normal aggregation or itteration measures (SUM, SUMX, etc) between these tables. Data in [User Count] does not filter [License Groups] for example. It goes the otherway. [License Group Mapping] doesn't filter [License Groups] either.
You need to turn on bi-directional filtering in your measure. so, if you dropped the description field in your matrix, to get to data in the [User Count] table to work, you need to enable bi-di filtering between [License Group Mapping] and [License Group]
Test measure =
CALCULATE(
SUMX(
UserCount,
UserCount[Value]
),
CROSSFILTER ( LicenseGroups[License Group], LicenseGroupMapping[License Group], BOTH )
)
the CROSSFILTER function within CALCULATE will change filter directions to ONE, BOTH, or NONE as desired. BOTH is the most common use of this.
Once you do that, you will have a path from the PCT Price List table all the way to the user Count table.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.