Sunday, May 22, 2022

How to Calculate SUMIFS, COUNTIFS, AVERGEIFS using Power Query List Functions in Power BI

How to use List.Count, List.Sum, List.Average, List.Min, List.Max Functions in Power Query
Scenario:
In the following scenario, we will discuss about how to perform the Calculations like the classic Excel Functions, SUMIFs or COUNTIFs or AVERAGEIFs using Power Query List functions.
Suppose we have sales table "tbl_Sales" as follows. Based on this table we will be calculating the CountIFs, SumIFs, AverageIFs.


Now from the above table, we
will be calculating the CountIFs, SumIFs, AverageIFs, MinIFs and MaxIFs in to the following Summary table "ds_SummaryStatistics".


The calculations should be done based on the following Conditional criteria:
tbl_Sales[CntryNam]=ds_SummaryStatics[Country] && tbl_Sale[ProdNam]=ds_SummaryStatics[Product]

Now lets calculate the 
the CountIFs, SumIFs, AverageIFs, MinIFs and MaxIFs using the List Functions of the Power Query in Custom Columns as follows:

The Count of Products, where tbl_Sales[CntryNam]=ds_SummaryStatics[Country] && tbl_Sale[ProdNam]=ds_SummaryStatics[Product] :

CountIFsList.Count(
                        Table.SelectRows(tbl_Sales,
                        (varCalc) => varCalc[CntryNam]=[Country]
                        and varCalc[ProdNam]=[Product] )
                    [ProdNam])

The Sum of Units Sold:
SumIFs = List.Sum(
                        Table.SelectRows(tbl_Sales,
                        (varCalc) => varCalc[CntryNam]=[Country]
                        and varCalc[ProdNam]=[Product] )
                    [Units_Sold])

The Average of Units Sold:
AverageIFs = List.Average(
                        Table.SelectRows(tbl_Sales,
                        (varCalc) => varCalc[CntryNam]=[Country]
                        and varCalc[ProdNam]=[Product] )
                    [Units_Sold])

The Maximum of the of Units Sold :
MaxIFs = List.Max(
                        Table.SelectRows(tbl_Sales,
                        (varCalc) => varCalc[CntryNam]=[Country]
                        and varCalc[ProdNam]=[Product] )
                    [Units_Sold])

The Minimum of the of Units Sold :
MinIFs = List.Min(
                        Table.SelectRows(tbl_Sales,
                        (varCalc) => varCalc[CntryNam]=[Country]
                        and varCalc[ProdNam]=[Product] )
                    [Units_Sold])

#Result:

The Complete Power Query is as follows:
let
Source = Excel.Workbook(File.Contents(DataSource), null, true),

ds_Sample_Sheet = Table.PromoteHeaders( Source{[Item="ds_Sample",Kind="Sheet"]}[Data], [PromoteAllScalars=true]),
ChangeType = Table.TransformColumnTypes(ds_Sample_Sheet,{{"Product", type text}, {"Country", type text}}),
ProductsCount = Table.AddColumn(ChangeType, "CountIFs", each List.Count(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product]
)[ProdNam])),
SumOfUnitsSold = Table.AddColumn(ProductsCount, "SumIFs", each List.Sum(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[Units_Sold])),
AveragUnitsSold = Table.AddColumn(SumOfUnitsSold, "AverageIFs", each List.Average(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product]
)[Units_Sold])),
MaxOfUnitsSold = Table.AddColumn(AveragUnitsSold, "MaxIFs", each List.Max(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[Units_Sold])),
MinOfUnitsSold = Table.AddColumn(MaxOfUnitsSold, "MinIFs", each List.Min(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product]
)[Units_Sold])),
#"Changed Type" = Table.TransformColumnTypes(MinOfUnitsSold,{{"Product", type text}, {"Country", type text}, {"CountIFs", Int64.Type},
{"SumIFs", Int64.Type}, {"AverageIFs", type number}, {"MaxIFs", Int64.Type}, {"MinIFs", Int64.Type}})
in
#"Changed Type"

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

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