Sunday, February 18, 2024

How to calculate Max Sale Date and Product details using DAX in Power BI

How to return Max Sale Date and Product with correct Totals using DAX in Power BI
Suppose we have simple Data Modal as follows.



The sample Data from ds_Sample is as follows:

----------------------------------------------------------------
Scenario 1:
Returning the Max Sale Date, and the Product that contributing the Max Sales


We can achieve this Scenario using the following DAX Functions.

Net Sales = SUM(ds_Sample[Qnty_Sold])

Max Sales By Date =
MAXX(
    VALUES(ds_Sample[Order_Date]),  
    [Net Sales]
   /* CALCULATE(SUM(ds_Sample[Qnty_Sold])) */
)

Max Sale Date =
CALCULATE( FIRSTNONBLANK(ds_Sample[Order_Date],1),
    FILTER( VALUES(ds_Sample[Order_Date]),
       [Net Sales] = MAX(ds_Sample[Qnty_Sold])
    )
    )

Max Sale Prod By Date =
CALCULATE( FIRSTNONBLANK(ds_Sample[Prod_Name],1),
    FILTER( VALUES(ds_Sample[Order_Date]),
       [Net Sales] = MAX(ds_Sample[Qnty_Sold])
    )
    )

Result:


Note:
The Total value 174 is the Max Total Sales by Date not the Total of 3 Max Sales.


----------------------------------------------------------------
Scenario 2: 
Returning the Unique Orders and Max Order value by Segment based on Order Date, with a Correct Total.

Max Sales = MAX(ds_Sample[Qnty_Sold])
Unique Orders = DISTINCTCOUNT(ds_Sample[Order_Date])

Note:
Please note that, I have considered the [Order_Date] for the Unique Orders calculation, only to explain this Scenario.

The [Order_Date] Column is having Duplicate Records, as we can expect the multiple Orders on the same date.

Now, to fulfil this Scenario, we will fix the Total Issue for [Max Sales] and [Unique Orders] by using the Measures as shown below:

Max Sales (Total) =
SUMX( VALUES(ds_Sample[Prod_Segment]),
    [Max Sales]
    /* CALCULATE(MAX(ds_Sample[Qnty_Sold])) */
    )

Unique Orders (Total) =
SUMX( VALUES(ds_Sample[Prod_Segment]),
    [Unique Orders]
    /* CALCULATE(DISTINCTCOUNT(ds_Sample[Order_Date])) */
    )

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Sunday, February 11, 2024

How to Calculate the Average of Averages using DAX in Power BI

How to Calculate the Average of Main Category based on Averages of Sub Category using DAX in Power BI
Scenario:
We have sample Dataset as follows, with various Dimension levels like:
Region > Country > Product_Segment > Product


This sample dataset has the Quantity of Products sold value (Qnty_Sold) either by Online or Offline (Sale_Mode).
Based on this Dataset, first we need to Calculate the Ratio of Online Sales out of Total Sales by Product_Segment , Country and Region.

The Average Online Sales of Product_Segment is Average of Average Online Sales by Product
The 
Average Online Sales of Country is Average of Average Online Sales by Product_Segment
The Average Online Sales of Region is Average of Average Online Sales by Country

We can achieve this Scenario as discussed below.

Net Sales = SUM(ds_Sample[Qnty_Sold])
Total Product Sales = CALCULATE([Net Sales], ALL(ds_Sample[Sale_Mode]))

Online Sales = CALCULATE( [Net Sales],
            FILTER( VALUES(ds_Sample[Sale_Mode]), ds_Sample[Sale_Mode]="Online")
                )

% Online Sales = DIVIDE([Online Sales]+0, [Total Product Sales],0)

Avg Online Sales per Prod =
AVERAGEX( VALUES(ds_Sample[Prod_Name]),
          CALCULATE([% Online Sales], ALL(ds_Sample[Sale_Mode]))
        )

Notes:
It is required to add the
[Online Sales]+0 , to show correct Average of Averages, by including the Blank() or Zero Values.
[Net Sales]: The amount of Product sales.
[Total Product Sales]: The total sales of Products across different [Sale_Mode] like Offline and Online.
[% Online Sales]: The Ratio of Online Sales out of Total Sales (Online + Offline).

[Avg Online Sales per Prod]: The Average Online sales per product



Note: 
[% Online Sales] = (291/664) = 0.438
[Avg Online Sales per Prod] = (0.315+0.710+0.281+0.506) /4 = 0.453

Since the Sales at the Prod_Name is the lowest level of granularity, the [% Online Sales] is same as [Avg Online Sales per Prod], when Prod_Name in the row context of the Visual.
---------------------------------------------------------------------
Now we will use the Average of [Avg Online Sales per Prod], to calculate the [Avg Online Sales per Prod_Segment], the above level of Prod_Name.


Avg Online Sales per Prod_Segment =
AVERAGEX( VALUES(ds_Sample[Prod_Segment]),
        CALCULATE([Avg Online Sales per Prod],
        ALL(ds_Sample[Sale_Mode]),ALL(ds_Sample[Prod_Name]))
        )


Note:
[Avg Online Sales per Prod_Segment] = (0.315+0.710+0.281+0.506) /4 = 0.453
---------------------------------------------------------------------
Similarly, we will use the 
Average of [Avg Online Sales per Prod_Segment] to calculate the [Avg Online Sales per Country], the above level of Prod_Segment.

Avg Online Sales per Country =
AVERAGEX( VALUES(ds_Sample[Country]),
           CALCULATE([Avg Online Sales per Prod_Segment],
                ALL(ds_Sample[Prod_Segment]),
                ALL(ds_Sample[Sale_Mode]), ALL(ds_Sample[Prod_Name])
            )
        )

Note:
[Avg Online Sales per Country] = (0.578+0.348+0.509)/3 = 0.478
---------------------------------------------------------------------
Similarly, we will use Average of [Avg Online Sales per Country] to calculate the [Avg Online Sales per Region], the above level of Country.

Avg Online Sales per Region =
AVERAGEX( VALUES(ds_Sample[Region]),
        CALCULATE([Avg Online Sales per Country],
            ALL(ds_Sample[Country]),ALL(ds_Sample[Prod_Segment]),
            ALL(ds_Sample[Sale_Mode]), ALL(ds_Sample[Prod_Name])
            )
        )


Note:
[Avg Online Sales per Region] = (0.478+0.487)/2= 0.482

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Saturday, February 3, 2024

How to ROLLUP Sub Total Count, Sum and Average values for each Hierarchy Level in SQL Server

How to calculate the ROLLUP Sub Totals and Grand Total Count, Sum and Average values for each Dimension Level in SQL Server
Scenario:
Suppose we have a sample table with Price values for different Hierarchy Levels like Region, Country, ProdGroup, ProdName, as shown below.

/* Drop the table if it arleady exists
DROP TABLE IF EXISTS [dbo].tbl_Sample;
*/

/* Create a Sample table */
CREATE TABLE [dbo].tbl_Sample (
    ProdID INT PRIMARY KEY,
    Region VARCHAR(50),
    Country VARCHAR(50),
    ProdGroup VARCHAR(50),
    ProdName VARCHAR(50),
    Price DECIMAL(10, 2)
);

/* Insert data into the sample table */
INSERT INTO [dbo].tbl_Sample (ProdID, Region, Country, ProdGroup, ProdName, Price)
VALUES
    (1, 'Europe', 'France', 'Electronics', 'Desktop', 900.00),
    (2, 'Asia', 'India', 'Appliances', 'Washing Machine', 1300.00),
    (3, 'Europe', 'Italy', 'Electronics', 'Tablet', 1000.00),
    (4, 'Asia', 'Japan', 'Appliances', 'Refrigerator', 1800.00),
    (5, 'Europe', 'France', 'Electronics', 'Laptop', 950.00),
    (6, 'Asia', 'India', 'Appliances', 'Microwave', 900.00),
    (7, 'Europe', 'Italy', 'Electronics', 'Desktop', 850.00),
    (8, 'Asia', 'Japan', 'Appliances', 'Washing Machine', 1400.00),
    (9, 'Europe', 'France', 'Furniture', 'Table', 450.00),
    (10, 'Asia', 'India', 'Furniture', 'Bed', 750.00),
    (11, 'Asia', 'India', 'Furniture', 'Chair', 250.00),
    (12, 'Europe', 'Italy', 'Furniture', 'Chair', 350.00),
    (13, 'Asia', 'Japan', 'Furniture', 'Table', 300.00),
    (14, 'Asia', 'Japan', 'Furniture', 'Desk', 400.00)

SELECT * FROM [dbo].tbl_Sample


Now can find the Sub Total for each Dimension (Country, ProdGroup) and Grand Total for the Main Level (Region).
/* Calculate Sub Total and Grand Totals for each Dimension Level using ROLLUP Function */
SELECT
    COALESCE(Region, '|Grand Total|') AS Region,
    COALESCE(Country, '|Total ' + Region + '|') AS Country,
    COALESCE(ProdGroup, '|Total ' + Country  + '|') AS ProdGroup,
    COALESCE(ProdName, '|Total ' + ProdGroup + '|') AS ProdName,
    COUNT(ProdID) AS TotalCount,
    SUM(Price) AS TotalPrice,
    CAST(AVG(Price) AS DECIMAL(10,2)) AS AveragePrice
FROM [dbo].tbl_Sample
GROUP BY ROLLUP (Region, Country, ProdGroup, ProdName);

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog