Combine the data in two tables and setup some measures.

[Lipora]

I have the following Two Tables:

1. Product Cost Table:

 

And a list of licenses:

From a previous post I was able to get help with showing the data in a matrix with counts of each license per person.  

There is a top section to the report now that I am trying for create and I am struggling with how to get the data I need together.

The top section is basically the Totals.  I would like each page to reflect a different city and to have an overview page on the front with totals of all licenses....  Something like this:

 I've tried setting up a relationship (one to many) between the Unit Cost table and the License List table using the "License" column in each table.  But when I try to add a colum to the License List to insert the unit cost for each item I keep getting an error that says that "Related" is not a proper key word.  I've also tried to create just a plain new column in the dataset and as soon as I go to name the column it disapears... I'm not sure what I am doing wrong.  Can someone help me first get a dataset that I can use that contains the data in the license list with the extra column of unit cost?

After that I will need to consider how I can show a set of totals per office per license on the Overview page and then a header for each Office on the subsequant pages with the detailed info..

[Greg_Deckler]

@Lipora - Not entirely following this, if RELATED does not work, try RELATEDTABLE. Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Lipora]

If I'm in the Data View, and I Right click the Dataset I am working with an select Add column, The Column will appear:

As soon as I click the three dots next to the column to rename it, it disappears:

If I try to  use a calculated column with either RELATED or RELATEDTABLE I get errors:

 

RelatedTable gives me:

 

I've seen  posts about Mcode vs. DAX formula as a possible problem but I've tried multiple ways to resolve this and can't seem to get the coumn in there.  I read through the common problems and I don't seem mine listed in there.  If you have a clue what I'm doing wrong maybe you could point me to the specific reference from there you think I should be catching?

Thanks.

[jdbuchanan71]

@Lipora 

A measure like this will use the cost from the product cost table and calcualte it for every value in your Licenses table.

Cost = SUMX ( Licenses, RELATED ( 'Product Cost'[UnitCost] ) )

You can use this measure against whatever categories from the Licenses table you want ( Name, Office, License etc ) and it should calcualte correctly.

[Lipora]

So looking at the example of where I am trying to get to with the totals, I'm not seeing how I can use that measure to have that reflect in the matrix table that is based on licenses at the moment.  The Matrix Table based off of the LIcense list looks like this::

 Ideally I would have a unit cost per license listed up to along with the license names, and at the bottom total costs per license type with a grand total for the whole office's licensing.

I can see from the measure it will get me the costs of any given license that I'm showing but how to I add that to the matrix?

[jdbuchanan71]

Just drag the meausre to the values well of the matrix.

[Lipora]

In this image above  the "Rate" is the unit cost of each license, the "Units Billed" is the number of licenses they have total accross all offices, and $Billed is the (rate * Units).

[v-yiruan-msft]

Hi @Lipora ,

What's your expected result? Do you want to create calculated columns or measures to get Units Billed and $Billed? If yes, please provide their calculation logic. Otherwise, please explain more details about your requirement. Thank you.

Best Regards

Rena