Formula Understanding

[Dharma]

Hi Guys,

Am beginner level in Power BI just trying to understand the below formula which I came across in my course, can anyone please break this formula because am not able to understand the below

1. Why 2 filter are used ?
2. Significance of countrows in this formula
3. Why cant we iterate directly by Sales growth measure instead of Total sales.?

Sales Per Growth Group =
CALCULATE([Total Sales],
FILTER(VALUES(Customer[Customer Names]),
COUNTROWS(
FILTER(‘Customer Banding’,
[Sales Growth_ADC] >= ‘Customer Banding’[Min] &&
[Sales Growth_ADC] < ‘Customer Banding’[Max])) > 0))

[Melissa]

Hi @Dharma,

Please see this Forum post by @Greg and all related content in there for more details.

Dynamic Grouping

Grouping in Power BI is often referred to as segmentation, chunking, binning, banding, or bucketing. For the purposes of this article, all will be referred to as the Dynamic Grouping DAX pattern. For example, you may want to know the group totals of aged invoices in a certain time period. So, first ensure your grouping column is available in the fact table (e.g., Invoices[Aging Days]). Aging Days = SWITCH( TRUE(), Invoices[Paid in Full] = 1, 0, Invoices[Paid in Full] = 0, INT( TODAY() …

Formatting goes a long way when trying to break down DAX logic.
The outer FILTER filters Customer Names so only rows are returned that meet a condition in the inner FILTER on the supporting 'Customer Banding’ table. DAX works from the inside out, so the inner condition is evaluated before the outer filter condition.

Sales Per Growth Group =
CALCULATE(
    [Total Sales],
    FILTER(
        VALUES( Customer[Customer Names] ),
        COUNTROWS(
            FILTER(
                'Customer Banding',
                [Sales Growth_ADC] >= 'Customer Banding'[Min]
                && [Sales Growth_ADC] < 'Customer Banding'[Max]
            )
        ) > 0
    )
)

.

1. There are 2 filters because 2 different tables are being filtered
2. Countrows is a test to see if the filter condition is met, is there is a row returned from the inner Customer Banding table? If so, the customer is kept and if it doesn’t survive the filter it’s omitted.
3. Sales Growth measure is used for the test in the inner filter condition and this calculation finally returns the Total sales value of all remaining Customer Names

I hope this is helpful.

[sedhosen]

Hi @Dharma,

Thnx for your question, and thnx to @Melissa for mentioning the related forum.
You absolutely will find the best resources in the mentioned forum by @Melissa, but I just wanted to share my experience learning how this formula work:

I interpreted the formula for myself from the bottom to the beginning.

• By the last Filter function, we actually are trying to categorize our measure (in your formula [Sales Growth_ADC] ) in one of the rows of the SUPPORTING TABLE ( ‘Customer Banding’ ).

• Since the result of this categorization would be a table (because the FILTER function is a table function) with zero OR one row, we use the COUNTROWS function to understand if the number of rows is one OR zero.
  If the number of rows would be zero, we would not need to account for that item, therefore we put the condition of “COUNTROWS(…)>0” for this function to only account for the items that are categorized in one of the rows of the SUPPORTING TABLE.

• The first FILTER function which should be used with the VALUES function, helps us to consider the above conditions for any distinct value of the column that would be placed in the VALUE function argument. For example, in the formula you mentioned, we are considering the amount of [Sales Growth_ADC] for each distinct [Customer Names].

• And finally, we use the popular, amazing CALCULATE function and place all the above formulas inside it because we need to use filters and also need to change the context of the formula which is a common situation in writing DAX.

I hope it would help
I know that it may not be as complete as the mentioned forum by Melissa, but I just wanted to share my point of view.

Best

[EnterpriseDNA]

Hi @Dharma, did the response provided by @sedhosen and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!