Forum Discussion
Bar Chart using only data entrys with highes Value
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!
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.
3 Replies
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.
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.
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.