Group yearly spend for suppliers

[bwiley]

I want to create groups by total spend and then put the spend per supplier in to those groups. If I do only one year at a time my code works fine but if I want to combine multiple years there is an issue. The company shows up in multiple categories if the spend amount match up to different categories. If I I put amount spent as a variable it works fine for multiple years but for individual years it shows in only one categroy regardless of the amount spent. 

Here is my code

 Total Amount Groups =

IF ( Diversity[Amount Paid] <= 10000, "0-10,000",

IF ( Diversity[Amount Paid] > 10000 && Diversity[Amount Paid] <= 50000, "10,001-50,000",

IF ( Diversity[Amount Paid] > 50000 && Diversity[Amount Paid] <= 100000, "50,001-100,000",

IF ( Diversity[Amount Paid] > 100000 && Diversity[Amount Paid] <= 200000, "100,001-200,000",

IF ( Diversity[Amount Paid] > 200000 && Diversity[Amount Paid] <= 300000, "200,001-300,000",

IF ( Diversity[Amount Paid] > 300000 && Diversity[Amount Paid] <= 500000, "301,000-500,000",

IF ( Diversity[Amount Paid] > 500000 && Diversity[Amount Paid] <= 999999, "501,000-999,999", "1,000,000+" ) ) ))))) 

OR

Total Amount Groups2 = 

VAR _ap2 =

CALCULATE(

    SUM(Diversity[Amount Paid]),

        FILTER('Diversity',Diversity[Vendor Name] = EARLIER(Diversity[Vendor Name]))

    )

VAR _tag = IF (

    _ap2 <= 10000,

    REPT(UNICHAR( 0032 ), "01")&"0-10,000",

    IF (

        _ap2 > 10000

        && _ap2 <= 50000,

        REPT(UNICHAR( 0032 ), "02")&"10,001-50,000",

    IF (

        _ap2 > 50000

        && _AP2 <= 100000,

        REPT(UNICHAR( 0032 ), "03")&"50,001-100,000",

        IF (

            _AP2 > 100000

            && _AP2 <= 200000,

            REPT(UNICHAR( 0032 ), "04")&"100,001-200,000",

            IF (

            _AP2 > 200000

            && _AP2 <= 300000,

            REPT(UNICHAR( 0032 ), "05")&"200,001-300,000",

                IF (

                _AP2 > 300000

                && _AP2 <= 500000,

                REPT(UNICHAR( 0032 ), "06")&"301,000-500,000",

                    IF ( _AP2 > 500000 && _AP2 <= 999999, REPT(UNICHAR( 0032 ), "07")&"501,000-999,999", REPT(UNICHAR( 0032 ), "08")&"1,000,000+" )

    )

)))))

These are calculated columns because I cant put a measure in a row from my understanding. 

I am assuming I would have to something with a seperate table but the solution is alluding me.

Any help?

Thanks!

[v-yueyunzh-msft · Accepted solution]

Hi , @bwiley 

Thank you very much for the detailed description and the data provided, which can make the problem clearer and simpler!~

First you want to use slicers for grouping, so computed columns can't support it,You need to write your grouping in the measure.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We need to create a dimension table:

And we do not need to create any relationship between them .

(3) Then we can create these measures:

Flag = IF( ISINSCOPE('Diversity'[Vendor Name]) , 
IF (SUM('Diversity'[Amount Paid]) <= 10000, "0-10,000",
IF (SUM('Diversity'[Amount Paid])> 10000 &&SUM('Diversity'[Amount Paid]) <= 50000, "10,001-50,000",
IF (SUM('Diversity'[Amount Paid])> 50000 && SUM('Diversity'[Amount Paid]) <= 100000, "50,001-100,000",
IF (SUM('Diversity'[Amount Paid]) > 100000 &&SUM('Diversity'[Amount Paid]) <= 200000, "100,001-200,000",
IF (SUM('Diversity'[Amount Paid])> 200000 && SUM('Diversity'[Amount Paid]) <= 300000, "200,001-300,000",
IF ( SUM('Diversity'[Amount Paid]) > 300000 && SUM('Diversity'[Amount Paid])<= 500000, "301,000-500,000",
IF (SUM('Diversity'[Amount Paid])> 500000 &&SUM('Diversity'[Amount Paid]) <= 999999, "501,000-999,999", "1,000,000+" ) ) ))))) , BLANK())

Value = var _cur_row = MAX('Table'[Row])
return
IF([Flag]=_cur_row , SUM('Diversity'[Amount Paid]) , BLANK())

Measure = SUMX(VALUES('Diversity'[Vendor Name]) , [Value])

End Measure = SUMX(VALUES('Table'[Row] ) , [Measure])

If you want to show the right total in the visual ,you can put the [End Measure] on the visual .

This is the result output:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bwiley]

@Ashish_Mathur Changed 0 to BLANK() in your two measures and starting value to 1 instead of 0 in the table and was able to use it in a matrix with Vendor Name. Your 2 measures are what I was missing in the puzzle as I already had the table to use. The calculatedtable part is what I wasnt getting right. 

@v-yueyunzh-msft Your solution also worked. I would have used it if not for the other solution and I defintely learned from it too.

Thank you all!!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-yueyunzh-msft]

Hi, @bwiley 

According to your description, you create two different columns as a dimension.

For your first column, this is determined for the Diversity[Amount Paid] value of each row, and if a row of the supplier satisfies a certain condition, it will be displayed in the matrix.

For your second column, you add calculated rules to group and categorize by suppliers. Then as long as your supplier names are equal and meet a certain condition, this supplier will only appear once.

I don't know your business logic very well, but according to your description, if you want to use years as a dimension and calculate the conditions of different years, you need to place Year as a dimension in the title of the matrix, or you can add the year judgment to your calculation logic to achieve your needs.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[bwiley]

Vendor Name	Vendor Number	Year	Amount Paid
ABC Co.	381589	2022	6000
ABC Co.	372729	2023	100
Google	393172	2022	1000000
Google	253190	2023	530000
Microsoft	253190	2022	2000000
Microsoft	253190	2023	350000
UPS	253190	2022	500
UPS	253190	2023	15,000
Fedex	253190	2022	1500
Fedex	253190	2023	500
USPS	253190	2022	2000
USPS	253190	2023	600000
Company A	253190	2022	350
Company A	253190	2023	25000
Company C	253190	2022	200
Company C	253190	2023	105000

Here is some sample data. I am using a slicer to choose the year. Since the companies listed have different amounts which fall in different categories it works fine if one year is selected. If both years are selected it shows them individually instead of summing the 2 years togethe.

I would like to put the company one time in one category based on the sum of the 2 years or if I selected 1 year it goes in the category based on spend only that year (second part works)

I have spend groups (Calculated column code listed in first post) and then Vendor Name as my rows

I have count of Vendor Name and Amount Paid in my values. 

[v-yueyunzh-msft · Accepted solution]

Hi , @bwiley 

Thank you very much for the detailed description and the data provided, which can make the problem clearer and simpler!~

First you want to use slicers for grouping, so computed columns can't support it,You need to write your grouping in the measure.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We need to create a dimension table:

And we do not need to create any relationship between them .

(3) Then we can create these measures:

Flag = IF( ISINSCOPE('Diversity'[Vendor Name]) , 
IF (SUM('Diversity'[Amount Paid]) <= 10000, "0-10,000",
IF (SUM('Diversity'[Amount Paid])> 10000 &&SUM('Diversity'[Amount Paid]) <= 50000, "10,001-50,000",
IF (SUM('Diversity'[Amount Paid])> 50000 && SUM('Diversity'[Amount Paid]) <= 100000, "50,001-100,000",
IF (SUM('Diversity'[Amount Paid]) > 100000 &&SUM('Diversity'[Amount Paid]) <= 200000, "100,001-200,000",
IF (SUM('Diversity'[Amount Paid])> 200000 && SUM('Diversity'[Amount Paid]) <= 300000, "200,001-300,000",
IF ( SUM('Diversity'[Amount Paid]) > 300000 && SUM('Diversity'[Amount Paid])<= 500000, "301,000-500,000",
IF (SUM('Diversity'[Amount Paid])> 500000 &&SUM('Diversity'[Amount Paid]) <= 999999, "501,000-999,999", "1,000,000+" ) ) ))))) , BLANK())

Value = var _cur_row = MAX('Table'[Row])
return
IF([Flag]=_cur_row , SUM('Diversity'[Amount Paid]) , BLANK())

Measure = SUMX(VALUES('Diversity'[Vendor Name]) , [Value])

End Measure = SUMX(VALUES('Table'[Row] ) , [Measure])

If you want to show the right total in the visual ,you can put the [End Measure] on the visual .

This is the result output:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly