Until now we have seen basic usages of DAX within Power Pivot, but now let's spice things up and use some nested DAX function to achieve interesting results while implementing Countif & Sumif in Power Pivot
Now unlike excel where applying Countif or Sumif is a straightforward affair, in DAX we have to utilize the Calculate function to nest our Count or Sum function within. Let’s see how it's done!
Data Used
Data Used
https://docs.google.com/spreadsheets/d/1dSc0XivD7W91Zi6EKCh-payKlU3NyGou1rkSTl7wxqA/edit?usp=sharing
Requirement
Create 2 new columns in Power Pivot
Apply COUNTIF based on City column
Apply SUMIF based on Profit column based on each City
Before we start with the requirements, it is necessary to understand few concepts related to CALCULATE function and its MODIFIERS
Understanding CALCULATE function
The syntax for CALCULATE function is
=Calculate( <expression>, <filter1>, <filter2>,...)
Now if you have not gone through my earlier content on Calculate, let me give you a brief summary. Calculate function lets you evaluate an expression (which usually is some kind of aggregation function), using the tabular data returned by the filter criteria which is also called a calculate modifier.
In today’s topic we will see a type of CALCULATE modifier which is called ALLEXCEPT, let us understand how it works
Understanding ALLEXCEPT function
The syntax for ALLEXCEPT function is
=ALLEXCEPT( <table>, <column>,...)
In simple terms, what ALLEXCEPT does is to instruct the engine to remove filters from everything except the columns mentioned within its parameters.
Now that you have a basic understanding of Calculate function & its modifier, let's complete the requirement now!
Applying COUNTIF in Power Pivot
Convert your data into a table and name it as per your choice. I’m going to name it as “ProfitTbl”
Select anywhere on the data & go to the Power Pivot tab and click on “Add to Data Model”
Now you should see the Power Pivot Editor. In this lets create a new calculated column to apply COUNTIF by entering below DAX
=CALCULATE(COUNTA(ProfitTbl[City]),ALLEXCEPT(ProfitTbl,ProfitTbl[City]))
DAX Explanation :
ALLEXCEPT - This returns a tabular data with filters removed from everything else except for City. Which means for each row it applies a filter for the city present in that row and shows the result
COUNTA - It’s similar to excel where its counting non-blank values in City column using the tabular data given by ALLEXCEPT
Finally Calculate evaluates everything together to give the final results
Applying SUMIF in Power Pivot
Similar to COUNTIF, we will use the same formula structure but use SUM instead of COUNTA. And use the Profit column.
=CALCULATE(SUM(ProfitTbl[Profit]),ALLEXCEPT(ProfitTbl,ProfitTbl[City]))
Now instead of a column you can use this formula inside a measure as well which will help you to drag these into a pivot table & see the result without duplicate entries like we see here.
Comments