Logic Visualizer | Excel

Table Sales Modified Summary

Create a modified table by summarizing and filtering the Sales table based on certain conditions and calculations. Group the table by product and calculate different metrics for each group.


Empty image or helper icon

This Query related with thread "DAX Code for Modifying Sales Table"

Prompt

Sales Modified = 
        SUMMARIZE(
            FILTER(
                ADDCOLUMNS(
                    ALL(Sales),
                    "Total Sales", [Qty] * [Price],
                    "Discount Sales", [Qty] * [Price] * (1 - [Discount])
                ),
                [Total Sales] <> [Discount Sales]
            ),
            [Product],
            "Total Regular Sales", SUM([Total Sales]),
            "Total Discounted Sales", SUM([Discount Sales]),
            "Discount Savings", SUM([Total Sales]) - SUM([Discount Sales])
        )

Answer

1. Create a new table Sales Modified by summarizing the following:
2. Filter the Sales table based on a condition.
3. Add two new columns to the filtered table: "Total Sales" and "Discount Sales".
4. Calculate the values for the new columns: [Qty] * [Price] for "Total Sales" and [Qty] * [Price] * (1 - [Discount]) for "Discount Sales".
5. Filter the table again to exclude rows where [Total Sales] is equal to [Discount Sales].
6. Summarize the filtered table by grouping it by [Product].
7. Calculate the following metrics for each group: 
   - "Total Regular Sales" by summing the values in the [Total Sales] column
   - "Total Discounted Sales" by summing the values in the [Discount Sales] column
   - "Discount Savings" by subtracting the sum of [Discount Sales] from the sum of [Total Sales].
8. Output the summarized table as Sales Modified.

Flowchart ```

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

This pseudocode outlines a process to create a modified table called "Sales Modified" by summarizing the original Sales table. The process involves filtering the table based on certain conditions, adding new columns, and calculating values for those columns. The table is then filtered again to exclude rows where the "Total Sales" is equal to the "Discount Sales". After that, the table is summarized by grouping it by the "Product" column. For each group, the process calculates metrics such as "Total Regular Sales", "Total Discounted Sales", and "Discount Savings". Finally, the summarized table is outputted as "Sales Modified".

More Logic Visualizers
Apache Flink Logic VisualizerApache Pig Logic VisualizerAzure Data Factory Logic VisualizerC/C++ Logic VisualizerCouchDB Logic VisualizerDAX Logic VisualizerExcel Logic VisualizerFirebase Logic VisualizerGoogle BigQuery Logic VisualizerGoogle Sheets Logic VisualizerGraphQL Logic VisualizerHive Logic VisualizerJava Logic VisualizerJavaScript Logic VisualizerJulia Logic VisualizerLua Logic VisualizerM (Power Query) Logic VisualizerMATLAB Logic VisualizerMongoDB Logic VisualizerOracle Logic VisualizerPostgreSQL Logic VisualizerPower BI Logic VisualizerPython Logic VisualizerR Logic VisualizerRedis Logic VisualizerRegex Logic VisualizerRuby Logic VisualizerSAS Logic VisualizerScala Logic VisualizerShell Logic VisualizerSPSS Logic VisualizerSQL Logic VisualizerSQLite Logic VisualizerStata Logic VisualizerTableau Logic VisualizerVBA Logic Visualizer