cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors