Need design help on Power BI modeling

[Sunkari]

Hi All,

I am struggling with following design issue. Please let me know if have any solution for this.

I have data in following format. Each column represents one KPI. Source is excel which contains many columns, for simplicity i have copied only KPI's data.

Spend	Recieved	PI	LI	PO
R	R	R	R	R
A	NA	NA	NA	NA
G	R	A	G	G
G	R	R	G	G
G	R	R	R	G
G	R	R	A	G
R	NA	NA	NA	NA
A	NA	NA	NA	NA
R	G	G	NA	NA
NA	G	G	NA	NA
A	G	G	NA	NA

User is exepecting data in following  grid/chart format in Power BI report. Count of Type with respective to each KPI.

Type	Spend	Recived	PI	LI	PO
R	3	5	4	2	1
G	4	3	3	2	4
A	3	0	1	1	0
NA	1	3	3	6	6

I am not getting any solution for above representation. I have created four Measures for Each KPI(Count of Type) total 20 mesures . But using 20 measures, i cann't get the above representation.

Could you guide me, how  to model Power BI to achieve this.

Note:I am not able to unpivot  source as excel contains many columns and above graph should be filtered by remaining columns.

Let me know if you need any further info.

[v-yulgu-msft · Accepted solution]

Hi @Sunkari,

Please refer to below steps.

In your source table, add several calculated columns:

Count Spend = CALCULATE(COUNT('KPI'[Spend]),ALLEXCEPT('KPI','KPI'[Spend]))

Count Recieved = CALCULATE(COUNT('KPI'[Recieved]),ALLEXCEPT('KPI','KPI'[Recieved]))

Count PI = CALCULATE(COUNT('KPI'[PI]),ALLEXCEPT('KPI','KPI'[PI]))

Count LI = CALCULATE(COUNT('KPI'[LI]),ALLEXCEPT('KPI','KPI'[LI]))

Count PO = CALCULATE(COUNT('KPI'[PO]),ALLEXCEPT('KPI','KPI'[PO]))

Then, create an auxiliary table:

KPI2 =
UNION (
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[Spend] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[Recieved] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[PO] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[PI] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[LI] )
)

Create a new table to dispaly the all available Type, and add calculated columns to count the Type with respective to each KPI. 

KPI3 = SUMMARIZE(KPI2,KPI2[Type])

Spend =
IF (
    LOOKUPVALUE ( 'KPI'[Count Spend], 'KPI'[Spend], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count Spend], 'KPI'[Spend], KPI3[Type] )
)

Recieved =
IF (
    LOOKUPVALUE ( 'KPI'[Count Recieved], 'KPI'[Recieved], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count Recieved], 'KPI'[Recieved], KPI3[Type] )
)

PI =
IF (
    LOOKUPVALUE ( 'KPI'[Count PI], 'KPI'[PI], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count PI], 'KPI'[PI], KPI3[Type] )
)

LI =
IF (
    LOOKUPVALUE ( 'KPI'[Count LI], 'KPI'[LI], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count LI], 'KPI'[LI], KPI3[Type] )
)

PO =
IF (
    LOOKUPVALUE ( 'KPI'[Count PO], 'KPI'[PO], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count PO], 'KPI'[PO], KPI3[Type] )
)

In report modeling, you can choose table visual to display above data view.

Best regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @Sunkari,

Please refer to below steps.

In your source table, add several calculated columns:

Count Spend = CALCULATE(COUNT('KPI'[Spend]),ALLEXCEPT('KPI','KPI'[Spend]))

Count Recieved = CALCULATE(COUNT('KPI'[Recieved]),ALLEXCEPT('KPI','KPI'[Recieved]))

Count PI = CALCULATE(COUNT('KPI'[PI]),ALLEXCEPT('KPI','KPI'[PI]))

Count LI = CALCULATE(COUNT('KPI'[LI]),ALLEXCEPT('KPI','KPI'[LI]))

Count PO = CALCULATE(COUNT('KPI'[PO]),ALLEXCEPT('KPI','KPI'[PO]))

Then, create an auxiliary table:

KPI2 =
UNION (
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[Spend] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[Recieved] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[PO] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[PI] ),
    SELECTCOLUMNS ( 'KPI', "Type", 'KPI'[LI] )
)

Create a new table to dispaly the all available Type, and add calculated columns to count the Type with respective to each KPI. 

KPI3 = SUMMARIZE(KPI2,KPI2[Type])

Spend =
IF (
    LOOKUPVALUE ( 'KPI'[Count Spend], 'KPI'[Spend], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count Spend], 'KPI'[Spend], KPI3[Type] )
)

Recieved =
IF (
    LOOKUPVALUE ( 'KPI'[Count Recieved], 'KPI'[Recieved], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count Recieved], 'KPI'[Recieved], KPI3[Type] )
)

PI =
IF (
    LOOKUPVALUE ( 'KPI'[Count PI], 'KPI'[PI], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count PI], 'KPI'[PI], KPI3[Type] )
)

LI =
IF (
    LOOKUPVALUE ( 'KPI'[Count LI], 'KPI'[LI], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count LI], 'KPI'[LI], KPI3[Type] )
)

PO =
IF (
    LOOKUPVALUE ( 'KPI'[Count PO], 'KPI'[PO], KPI3[Type] ) = BLANK (),
    0,
    LOOKUPVALUE ( 'KPI'[Count PO], 'KPI'[PO], KPI3[Type] )
)

In report modeling, you can choose table visual to display above data view.

Best regards,
Yuliana Gu

[Sunkari]

@v-yulgu-msft: Thanks for your time. Let me try that.

[v-yulgu-msft]

Hi @Sunkari,

Have you resolved your issue? If above advice is helpful to your scenario, please kindly mark it as an answer so that more people having similar requirements can benifit from it. If you still have any question, please feel free to ask.

Regards,
Yuliana Gu

[Sunkari · Accepted solution]

@v-yulgu-msft Thanks for your help.  I hope your's best solution for this problem. I followed a different approach where i duplicated table for each KPI with two extra columns (KPIName, Value) and done a union all of those tables, used final table for reporting purpose.