Calculation within a table visual

[VulcanPromance]

Hi.

Lets say I have a table with products called Products. It contains a product name, a category name and a price.
In the visual I want to show Categories, Products (there can be several under each category) and price, but in the same visual I also want to calculate Markup 3%, and Transfer Pricing 5% and 12% Tax witholding based on various variable.. Like markup for all categories, transfer pricing for just a few, and tax witholding for just 1. Is this possible? Its quite easy in excel, but Power Bi has me baffled on this thing that seems so easy.

[PaulDBrown · Accepted solution]

@VulcanPromance 

Thank you for providing the sample data (I see the criteria differ from your original post).

Here is how I have gone about this.

1) Create a table including the categories and rows you will be using in your visual.

Firstly, reference a new table to the data table and delete all columns except Category and product.

Next create a new table ("Enter Data") with two columns: Category and Product and type in the values for your extra rows in your visual:

Now Append these two tables to create a new table (I've called it "Visual Rows") including all categories and products as in:

 Load into the model, and make sure there are no relationships between your tables:

2) create the calculation measures:

Sum of Cost = SUM(Data[Cost])

Markup 3% = CALCULATE([Sum of Cost] * 0,03)

Tranfer Pricing 5% = CALCULATE([Sum of Cost] * 0,05;
                        FILTER('Data';
                            'Data'[Product] = "A" || 'Data'[Product] = "B" ))

Withold Tax = CALCULATE([Sum of Cost] * 0,12;
                FILTER('Data';
                'Data'[Product] = "A"))

3) now you need to create new measures to adapt to the row structure of the table/visual:

Sum of cost (visual) = CALCULATE([Sum of Cost]; TREATAS(VALUES('Visual rows'[Product]); 'Data'[Product]))

Cost (calc) = SWITCH(TRUE();
        SELECTEDVALUE('Visual rows'[Product]) = "Markup 3%"; [Markup 3%];
        SELECTEDVALUE('Visual rows'[Product]) = "Transfer Pricing 5%"; [Tranfer Pricing 5%];
         SELECTEDVALUE('Visual rows'[Product]) = "Withhold Tax 12%"; [Withold Tax];
         [Sum of cost (visual)])

Cost (for Visual) = 
SUMX(SUMMARIZE('Visual rows'; 'Visual rows'[Product]; "Cost1"; [Cost (calc)]); [Cost1])

4) you can now create your visual using the Category and Product from your new table (in my case table "Visual Rows") and the last measure created [Cost (for Visual)].

to get this:

and here is the PBIX file if you're interested:

Row structured visual 

EDIT: Apologies since I live in a land (local settings) where;

- the decimal separator is a " , " instead of a " . "

- the thousands separator is a " . " instead of a " , "

- the separator in measures is a " ; " instead of a " , "

[VulcanPromance]

This is simply brilliant. I wish I had this knowledge of Power Bi..

[PaulDBrown]

@VulcanPromance 

Thanks for your comment. I very much appreciate it! 

[PaulDBrown]

@VulcanPromance 

Can you please provide a table with sample data? (dummy data if confidential)