Not able to create proper ageing bucket from Tableau to Power BI

[MR007]

we have created Spend Bucket using below Tableau formulas:

Payment Per Account = { FIXED [Vendor Name]:sum(ABS([Grss Amnt Usd]))}

using this have created Spend Bucket as below:

Spend Bucket = 

IF[Payment Per Account]<=1000000 then "0-1M"
ELSEIF [Payment Per Account]>1000000 and [Payment Per Account]<=5000000 THEN "1-5M"
ELSEIF [Payment Per Account]>5000000 and [Payment Per Account]<=10000000 THEN "5-10M"
ELSEIF [Payment Per Account]>10000000 and [Payment Per Account]<=100000000 THEN "10-100M"
ELSEIF [Payment Per Account]> 100000000 then ">100M"

END

i have migrated these formulas in Power BI as below:

have created below measure,

Payment_per_account=CALCULATE(ABS(SUM(tm_ce_bussiness_scorecard_fact[grss_amnt_usd])),ALLEXCEPT(tm_ce_bussiness_scorecard_fact,tm_ce_bussiness_scorecard_fact[Vendor Name]))

and column for Spend Bucket as,

Spend Bucket = SWITCH(TRUE(),

[Payment per account_test] <= 1000000, "1) 0-1M",

[Payment per account_test] >1000000 && [Payment per account_test] <= 5000000 , "2) 1-5M",

[Payment per account_test] >5000000 && [Payment per account_test]<= 10000000 , "3) 5-10M",

[Payment per account_test] >10000000 && [Payment per account_test] <= 100000000 , "4) 10-100M",

[Payment per account_test] >100000000 , "5) >100M")

This is giving me exact numbers but the numbers are getting mismatched as soon as I am filtering by month or year.

Please suggest/help me to resolve this issue.

Thanks in Advance.

[v-shex-msft]

Hi @MR007,

Did you mean to create a calculated field that can recognize these records to set the range category of them? If that is the csae, any regular in these conditions to setting categories?
AFAIK, these types of formulas are defined by 'start/end' range, excepted ranges, ranges with regular offsets. If your scenario meets this, you can refer to the following formula to use one dynamic condition to replace multiple regular range conditions.

Spend Bucket =
VAR currPay = [Payment per account_test] / 1000000
VAR offset = 5
VAR _rate =
    INT ( currPay / offset )
RETURN
    SWITCH (
        TRUE (),
        //start
        currPay <= 1, "0-1M",
        //first
        currPay > 1
            && currPay <= offset, "1-5M",
        //regular ranges
        currPay > _rate * offset
            && currPay <= ( _rate + 1 ) * offset,
            _rate * offset & "-" & _rate * offset & "M",
        //last
        currPay > 96
            && currPay <= 100, "96-100M",
        //end
        currPay > 100, ">100M"
    )

Regards,
Xiaoxin Sheng

[lbendlin · Accepted solution]

You can simplify the spend bucket formula

Spend Bucket = SWITCH(TRUE(),
[Payment per account] <= 1000000, "1) 0-1M",
[Payment per account] <= 5000000 , "2) 1-5M",
[Payment per account] <= 10000000 , "3) 5-10M",
[Payment per account] <= 100000000 , "4) 10-100M",
"5) >100M")

If you don't want your month or year filter to impact the bucket then you need to add that exclusion to your measure   (something like ALL(Year) etc )

[Mhnsan1113]

still facing the issue, proper bucketing not happening. created 2 formulas

1) measure: 

1.gross-amt-ALLexcept = CALCULATE([gross amt ABS],ALLSELECTED(tbvw_bussiness_scorecard_pot_vw[vendor name]),ALLEXCEPT(tbvw_bussiness_scorecard_pot_vw,tbvw_bussiness_scorecard_pot_vw[vendor name]))

column

1aa.spend bucket_ALL Except =

IF([1.gross-amt-ALLexcept]<=1000000, " 0-1M",

IF([1.gross-amt-ALLexcept]>1000000 && [1.gross-amt-ALLexcept] <=5000000, " 1-5M",

IF([1.gross-amt-ALLexcept]>5000000 && [1.gross-amt-ALLexcept] <=10000000 , " 5-10M",

IF([1.gross-amt-ALLexcept]>10000000 && [1.gross-amt-ALLexcept]<=100000000 , " 10-100M",

" >100M"

))))

outside dashboard filter not getting applied e.g below

i select one vendor name and highligted in yellow are my above calculated fields. as soon as i filter i get the spend bycke >100m & 1.gross-amt-ALLexcept is 131,923,680 no issues.  As soon as i filter for say Jan'20

its still givin me 131923680 and >100m actually the amount should have shown 18548491 and bucket should have been 10-100M

Can you please how to fix this

Thanks in adavance