Excel Forum

Accessing column data from a virtual table, or building a base table? (Pareto Chart)

Steve Brown

New Member

Members

Forum Posts: 1

Member Since:
June 23, 2021

Offline

June 23, 2021 - 5:27 pm

This is an extension to an earlier issue on a different forum. (https://community.powerbi.com/.....909#M41327)

I have a table that has many columns, but for the purposes of this example looks like this (call it Order Table)

Order ID	Item Code
O-001	I-001
O-001	I-002
O-001	I-004
O-002	I-001
O-002	I-003
O-003	I-002
O-003	I-003
O-004	I-001

In my measure I create a Summarized table which tells me how many times an Item Code occurs in the above table. Effectively how many orders are using that Item Code. So based on the above it would look like this;

Item Code	Occurs
I-001	3
I-002	2
I-003	2
I-004	1

The above is therefore a virtual table in my Measure on the Order Table. The DAX to create the virtual Table is as follows

VAR ItemTable= SUMMARIZE(ALLSELECTED(OrderTable), OrderTable[Item Code], "Occurs", DISTINCTCOUNT(OrderTable[Order Id]))

What I want to do in my Measure now is access this virtual table to find the 'Occurs' value for the Item Code in the current context. The context being based upon OrderTable because that is where the measure is.

The long term intention is to create a measure that allows me to build a Pareto Graph, but I think my strategy is wrong because the data for the graph is largely in the virtual table. My virtual table just needs a cumulative sum of the Occurs column, built in descending order of that column. I think I need to create another base table, probably using Power Query, then use that new table to make my chart.

I would appreciate some advice / guidance please.

TIA

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1810

Member Since:
November 8, 2013

Offline

June 25, 2021 - 3:38 pm

Hi Steve,

To sort that table, you can try the SAMPLE function:

SAMPLE(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])

for <n_value> value, you can use:

DISTINCTCOUNT(OrderTable[Item Code])

But, if you add a pivot table from OrderTable and add the Item Code into rows section, and any field into Values section as a Count,

this will return what you have in the virtual table without any measures needed, not sure what you are trying to do.