getting the cost based on category

Question

I have tables like this:Project&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CostA&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CPU&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 123A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Network&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 222B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CPU&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 221&nbsp;Then I have another two table which is for CPU and Network like this: Table for CPU&nbsp;Project&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Region1_CPU Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Region2_CPU Value&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.5B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.8C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp;&nbsp;&nbsp;&nbsp;same I have for Network:Project&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Region1_Network Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Region2_Network Value&nbsp;A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.8C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp;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.&nbsp; 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.&nbsp;Thanks

lbendlin · Answer

Your source data is in rather unfortunate shape. Here are some transforms to make it usable:
Costs:
&nbsp;
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"
&nbsp;
CPU:
&nbsp;
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"
&nbsp;
Network:
&nbsp;
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"
&nbsp;
&nbsp;
This will then allow you to combine the fact tables
Values:
&nbsp;
let
    Source = CPU &amp; Network
in
    Source
&nbsp;
&nbsp;
Now you can load the data.&nbsp; Normally you should have separate tables for projects, categories and regions to make it a better data model.
&nbsp;

&nbsp;
From there you can now calculate all the required values
&nbsp;

&nbsp;
As you can see the column totals are incorrect.&nbsp; 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.

zzzsharepoint · Answer

lbendlin&nbsp;why the total comes out wrong?

lbendlin · Answer

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 · Answer

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

lbendlin · Answer

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

Forum Discussion

getting the cost based on category

7 Replies

Related content

Get Minimum Date per Category based on Minimum Value per Category

Multiplying based on category

Index column based on value/category

Conditional formatting for category axis labels based on Budget vs Actual

Average of categories over vendor

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

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