cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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,

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.

1 ACCEPTED SOLUTION
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 )

3 REPLIES 3
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.
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 )

Frequent Visitor

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

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.