cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MR007
Frequent Visitor

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

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.
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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 )

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
lbendlin
Super User
Super User

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 )

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
Mhnsan1113_0-1630052880878.pngMhnsan1113_1-1630052957873.png

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

 

Mhnsan1113_4-1630053376690.pngMhnsan1113_5-1630053407233.png

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

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!