Tuesday, 12 December 2017

How to Create a New Calculated Table from Existing Table in Power BI

Creating a New Calculated Table from Existing Table in Power BI
We can Create a New Table with Single Column using the "DISTINCT" Function on Existing Table. Also, we can create a New Table with Multiple Columns using the "SUMMARIZE" Function on Existing Related Tables in Power BI.

1)Creating a New Table with Single Column from One Existing Table:
Suppose we have a Table "vSalesByCustRegion" with Multiple Fields as follows.



From this Table, we wants to extract the Distinct Product Names into a New Table called "Tbl_DistProdNames".

A)Go to Power BI Report > Modeling Tab > New Table
Click on the New Table 



B)Next Enter the DAX Expression/Formula to Create the Table
Enter the below DAX Formula in the Formula Bar

Tbl_DistProdNames = DISTINCT(vSalesByCustRegion[Prod_Name])



Next press Enter Key , it will Create a New Table Tbl_DistProdNames with One Field "Prod_Name"

Result :



2)Creating a New Table with Multiple Columns from One Existing Table :
Here want to Create a New Table "Tbl_CustDetails" with Multiple Columns from an Existing Table.

Enter the below DAX Formula in the Formula Bar

Tbl_CustDetails = SUMMARIZE(vSalesByCustRegion,vSalesByCustRegion[Cust_Name],vSalesByCustRegion[Cust_Segment],vSalesByCustRegion[Region_Name])

Result :

3)Creating a New Table with Multiple Columns from Multiple Related Tables :

Here want to Create a New Table "Tbl_CustProdSup" with Multiple Columns from an Existing Related Table.
Please note when we are creating a New Table from Multiple Tables, there should be a defined relation between them.

Here in my Data Model, I have one Fact Table and 3 Dimension Tables , which have already a Relation between them.


When we are Creating a New Table using Summarize Formula , Once we enter the Summarization Table Name (here the FactSales Table ) it will show the Related Table Names and their Fields that we can use for Group By.


Enter the below DAX Formula in the Formula Bar

Tbl_CustDetails = SUMMARIZE(vSalesByCustRegion,vSalesByCustRegion[Cust_Name],vSalesByCustRegion[Cust_Segment],vSalesByCustRegion[Region_Name])

Result :


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

Monday, 11 December 2017

How to Calculate the Cumulative Sum or Running Total in Excel

Excel Formula to the Calculate the Cumulative Sum or Running Total
The Cumulative Sum will be formed by or resulting from accumulation or the addition of successive parts or elements.
This Cumulative Sum is useful in analyzing the Business data, for example to identify the Sales Growth, by Calculating the Sum of Sales for MTD, QTD and YTD. 

Example:
Suppose we have the Sales data by Region, Product for 3 Quarter as follow..


Now We can calculate the Cumulative Sum with No Condition, with One Condition and with Multiple Conditions as follows..

Cumulative NetSales(with No Condition) :

Enter the below formula in Range ("F2") and drag till Range ("F13"). Now the Formula looks as follows, where the Range Begin is Locked at Column & Row level and the Range End is only Locked at Column level.


Range ("F2")=SUM($E$2:$E2)
Range ("F3")=SUM($E$2:$E3)
----
----
Range ("F13")=SUM($E$2:$E13)



Cumulative NetSales(with Condition on Region):

Enter the below formula in Range ("G2") and drag till Range ("G13"). Here we applied one Condition on Region which calculate the Cumulative Sum for each Region. The Cumulative Sum will break by each Region.


Range ("G2")=SUMIF($B$2:$B2,$B2,$E$2:$E2)
Range ("G3")=SUMIF($B$2:$B3,$B3,$E$2:$E3)
----
----
Range ("G13")=SUMIF($B$2:$B13,$B13,$E$2:$E13)

Cumulative NetSales(with Condition on Region and Product):
Enter the below formula in Range ("H2") and drag till Range ("H13"). Here we applied one Condition on Region and Product which calculate the Cumulative Sum for each Region by Product. The Cumulative Sum will break by each Product of that Region.


Range ("H2")=SUMIFS($E$2:$E2,$B$2:$B2,$B2,$C$2:$C2,$C2)
Range ("H3")=SUMIFS($E$2:$E3,$B$2:$B3,$B3,$C$2:$C3,$C3)
----
----
Range ("H13")=SUMIFS($E$2:$E13,$B$2:$B13,$B13,$C$2:$C13,$C13)

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

Sunday, 10 December 2017

How to Merge Two Queries or Tables in Power BI

Merging Two Queries or Tables in PowerBI
Merge is a Technique of Combining Two Queries or Tables in Power BI. We can Merge Two Tables based on the Key Column of both the Tables.
Merge will works in the same way as SQL Joins.
Scenario :
Suppose we have tables which we want to Merge as follows..

LT_CustByRegion ( Left Table )


RT_CustBySegment ( Right Table )


Now we will Merge, these Two Tables into a New Table using different Joins based on the Key Colum "CustId", as shown below.

1)Go to Query Editor > Home > Merge Queries > Merge Queries as New

Here we are selecting the "Merge Queries as New" , as we are merging Queries into a New Table.




2) Next Select the Merge Join Kind (Left Join or Right Join or Inner Join or Full Outer Join or Left Anti Join or Right Anti Join )

A) Merge Join Kind : Left Outer
Here we are using the Left Outer Join , which will return the all rows from Left Table and matching rows from Right Table.

First we should select the Common Key Column from both the Tables and then we have to select the Join Kind.


Now the Merge Left output Table is generated where the Right Table output is added in a New Column at the last.


Now We need to expand the Right Table and select the required Columns from Right Table that we want in Merge Left output.

Here I am selecting only the "CustSegment" from Right Table into Merge Output


Result :
All the rows from Left Table and Matching rows from Right Table.
The non Matching values will shown as "null"


B) Merge Join Kind : Right Outer
When we select the Right Outer Join , it will return all rows from Right Table and matching rows from Right Table.

Result :
All the rows from Right Table and Matching rows from Left Table.
The non Matching values will shown as "null"


If we select all the Columns from Right Table, then result as follows..


C) Merge Join Kind : Inner Join
When we select the Inner Join , it will return only the matching rows from both the Tables.

Result :
The matching rows from both the Tables.


D) Merge Join Kind : Full Outer Join
When we select the Full Outer Join , it will return all the rows from both the Tables.

Result :
All the rows from both the Tables.


E) Merge Join Kind : Left Anti Join
When we select the Left Anti Join , it will return only the rows from Left Table which does not have the matches in Right Table.

Result :
The only rows from rows from Left Table which does not have the matches in Right Table.


F) Merge Join Kind : Right Anti Join
When we select the Right Anti Join , it will return only the rows from Right Table which does not have the matches in Left Table.

Result :
The only rows from rows from Right Table which does not have the matches in Left Table.


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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts