getting the cost based on category

[zzzsharepoint]

I have tables like this:

Project	Category	Cost
A	CPU	123
A	Network	222
B	CPU	221

Then I have another two table which is for CPU and Network like this: Table for CPU

Project	Region1_CPU Value	Region2_CPU Value
A	0.5	0.5
B	0.2	0.8
C	0.3	0.7

same I have for Network:

Project	Region1_Network Value	Region2_Network Value
A	0.3	0.7
B	0.2	0.8
C	0.3	0.7

Now I need to show the visual for each project where I determine the cost of CPU and Network for Region 1 and Region 2.  Basically I need to calculate Cost ie. and the formulae for that is:

Project A:
CPU cost from Table 1 for Project A is 123 so for Region 1 and Region 2 it is 0.5*123 and 0.5*123 and Network cost is 222*0.3 for region1 and 222*0.7 for region2. same way for other project. Can anyone please guide how can I do this.

Thanks

[zzzsharepoint]

@lbendlin why the total comes out wrong?

[lbendlin]

Depending on the visual type your measure can be computed multiple times, each in its own filter context. In a table visual the calculation for the total is independent of the calculation for each row.

[zzzsharepoint]

Is there a way we can show the table with values and right total in a visual?any work-around?

[lbendlin]

what is the "right" total?  What number would you expect to see? The average? The sum?

[zzzsharepoint]

I guess Total means Sum.. Thats what i would lIke to show

[lbendlin]

I think I already corrected that in the pbix.  Check it again please.

[lbendlin]

Your source data is in rather unfortunate shape. Here are some transforms to make it usable:

Costs:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVLADpwDQnFLAoGhkbFSrA5eI/xSS8rzi7LxGmNkZAQ2xol8lxgZGSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"                     Category " = _t, #"                         Cost" = _t]),
    TCN = Table.TransformColumnNames(Source,each Text.Trim(_)),
    #"Trimmed Text" = Table.TransformColumns(TCN,{{"Project", Text.Trim, type text},{"Category", Text.Trim, type text},{"Cost", Text.Trim, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Trimmed Text",{{"Cost", Currency.Type}})
in
    #"Changed Type"

CPU:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUnBU0lFSIAAM9EwJqQIpidWJVnIibJyBnhFh0yzApjkTY5oxYdPMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"                 Region1_CPU Value" = _t, #"                       Region2_CPU Value" = _t]),
        TCN = Table.TransformColumnNames(Source,each Text.Trim(_)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TCN, {"Project"}, "Region", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Project", Text.Trim, type text},{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Region", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Region", "Category"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type number}})
in
    #"Changed Type"

Network:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUnBU0lFSIAAM9IwJqTLQM1eK1YlWciJsnIGeEWHTLMCmORNjGnFuiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"                 Region1_Network Value" = _t, #"                      Region2_Network Value" = _t]),
        TCN = Table.TransformColumnNames(Source,each Text.Trim(_)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(TCN, {"Project"}, "Region", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Project", Text.Trim, type text},{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Region", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Region", "Category"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", type number}})
in
    #"Changed Type"

This will then allow you to combine the fact tables

Values:

let
    Source = CPU & Network
in
    Source

Now you can load the data.  Normally you should have separate tables for projects, categories and regions to make it a better data model.

From there you can now calculate all the required values

As you can see the column totals are incorrect.  That can be fixed by adjusting the measure according to your needs (or by not showing the totals).

Total = 
var p = values(Projects[Project])
var r = VALUES(Regions[Region])
var c = VALUES(Categories[Category])
var a = crossjoin(p,r,c)
var b = ADDCOLUMNS(a,"co",var p=[Project] var c=[Category] return CALCULATE(sum(Costs[Cost]),Costs[Category]=c,Projects[Project]=p),
                     "va",var p=[Project] var r=[Region] var c=[Category] return CALCULATE(sum('Values'[Value]),Costs[Category]=c,Regions[Region]=r,Projects[Project]=p))
return sumx(b,[co]*[va])

see attached.