Forum Discussion

VulcanPromance's avatar
6 years ago
Solved

Calculation within a table visual

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 ...
  • PaulDBrown's avatar
    6 years ago

    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 " , "