Forum Discussion
Calculation within a table visual
- 6 years ago
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:
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 " , "