Bar Chart using only data entrys with highes Value

[Anonymous]

I have a small data set ("Sheet1") that contains transactions. A transaction is definied by a customer who buys something - however it is not important who sold the product. The seller column is called "used in company".

Each transactions has a Transcation ID ("Tx-ID") and is associated with a Quantity of sold products ("Quantity"), a special profitability measure ("affordability") and a basic CM1 value ("CM1"). Customer and product info is not included. 

Now, since transactions can have different sellers (or "used in companies") - some transactions occur multiple times in the data set. These double transactions differ only in the affordability and CM1.

When working with the data, only those transactions with the highest affordability should be considered. However, this should be done with regards to one or multiple used in companies that the user has selected.

As an example, we have the following line items:

Affordability	used in company	Tx-ID	Quantity	CM1	month
230	New York	1	300	1000	January
10	Berlin	1	300	200	January
20	Berlin	2	400	2000	January

For used in company = New York & Berlin, CM1=3000 and Quantity = 700.

For used in company = Berlin, CM1 = 2200 and Quantity = 700

Now what I want to do is create a stacked bar chart, with:

- Y-Axis: CM1

- X-Axis: month & Tx-ID (to show a consolidated picture while still allowing for a drill down)

- Legend: Used in company

So sticking to the example from above:

On the consolidated monthly view, having all companies selected, the chart should display CM1 of 3000 (since only the CM1 associated with the higest affordability per Tx-ID is considered) with a 2000:1000 split.

When jumping to the Tx-ID level, I want to see two Transactions, where Tx-ID 1 belongs to New York and Tx-ID 2 belongs to Berlin.

This chart should also be adaptive to a "used in company"-slicer: Thus, used in company = Berlin, Tx-ID 1 as well as Tx-ID 2 should be associated with Berlin (since now for both transactions Berlin has the highest affordability) and on a consolidated view, the CM1 should be 2200.

How can I achieve that?

Thanks a lot for some wise advice!

[Anonymous]

Thanks a lot for the reply. Its a good start but not exactly what I was looking for yet.

I your shared solution, when I select all used in companies, all three transactions are considered in the chart. However, when I select New York and Berlin, it should be checked if they share a Tx ID (which they do, Tx ID = 1) and if so, display only the one with the highest affordability (so in this Case the New York transaction).

And the same then also holds on the consolidated monthly view.

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

Just modified the measure to the following code

Measure = var a=MAXX(FILTER(ALLSELECTED('Table'),[Tx-ID] in VALUES('Table'[Tx-ID])&&[month] in VALUES('Table'[month])),[CM1])
return IF(ISFILTERED('Slicer'[used in company]),IF(COUNTROWS(VALUES(Slicer[used in company]))=CALCULATE(DISTINCTCOUNT('Table'[used in company]),ALL('Table')),CALCULATE(SUM('Table'[CM1]),FILTER('Table',[CM1]=a)),CALCULATE(SUM('Table'[CM1]),FILTER('Table',[used in company] in VALUES('Slicer'[used in company])))),CALCULATE(SUM('Table'[CM1]),FILTER('Table',[CM1]=a)))

Best Regards!

Yolo Zhu

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

[v-xinruzhu-msft]

Hi @Anonymous 

You can refer to the following example

The data table is the same as your offered.

Slicer table

Then you can create a measure and put the measure to y-axis

Measure = var a=CALCULATE(MAX('Table'[Tx-ID]),ALLSELECTED('Table'),'Table'[used in company] in VALUES('Table'[used in company]))
return IF(ISFILTERED('Slicer'[used in company]),CALCULATE(SUM('Table'[CM1]),FILTER('Table',[used in company] in VALUES('Slicer'[used in company]))),CALCULATE(SUM('Table'[CM1]),FILTER('Table',[Tx-ID]=a)))

Output

Best Regards!

Yolo Zhu

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