Forum Discussion
Calculate average percentage value and multiply by value through bridge table
I've found a few helpful posts, but I haven't quite been able to track this one. I have a data model in Excel I've developed to try and scope the software licensing for a fairly complicated product with a broad user base.
What I am trying to do is multiply the value in the UserCount table by a percentage value in the LicenseGroupMapping table to return the amount of each license that will be required.
The think I have the relationship right by creating two measures
SUMX(UserCount,UserCount[Value])
SUMX(LicenseGroupMapping, LicenseGroupMapping[Value])
and then another measure to multiply these by each other. This works OK if I create a pivot table grouped by the License group field. But completely breaks if I create a pivot table using the software description instead.
What I'm struggling with the second measure as what I would like it to do is average the percentage value based on the filter context. i.e. two rows with a value of 10% would just be 10% but if I have two values of 10% and 50%, I don't want the result of 60%
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.
1 Reply
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.