Help to create data model in Powerpivot for Flat table (fact table)

[_google]

Hi Experts



Looking for suggestion on Data model(PowerPivot) with Fact and dimension for below Fact table on which i need to measure KPI for "ZONE" with filter option on Area and Region [ fact Table ( below is example of 60000 R and 90 C) ]


I have create 3 dimension tables 1. Area ( Primary key 60 unique values), Sub Area ( Primary key 130) and ZONE. But struggling to get right combination of relationship

Aggregation method suggestive using SUMIFS



FillterArea
FillterSub Area

ZoneQTY
AAASUMIFS( QTY, AAA>=0,AAA<=3, Area, Filter Selection, Sub Area, Filter Selection)
BBBSUMIFS( QTY, DDD>=0,AAA<=3, Area, Filter Selection, Sub Area, Filter Selection)
CCCSUMIFS( QTY, CCC>=0,AAA<=3, Area, Filter Selection, Sub Area, Filter Selection)
DDDSUMIFS( QTY, DDD>=0,AAA<=3, Area, Filter Selection, Sub Area, Filter Selection)




Fact table

TypeAreaSub AreaQTYAAABBBCCCDDD
AFGV1102022
AFGV102021
AFGV2100011
ATFV1100221
ATFV1103121
ATFV1202112
ARGV2100111
ARGV1100231
ARGV2102111

 

[NARAYANK991]

Hi ,

Instead of posting sample data , can you upload your workbook which has the actual work data ?

Narayan

 

[_google]

Hi Narayan,

I cannot upload actual data but the structure of my file is mirror image of actual data.

What i want to achieve and required. Flat file structure i cannot change to tabulate ZONE however client need to measure Zone based on the tolerance limit and also have filter functionality based on Area and Region.

regards

 

[NARAYANK991]

Hi ,

I am sorry but I cannot help out since my version of Excel is 2010.

Hopefully @Chihiro can help.

Narayan

 

[Chihiro]

You are loading data to data model directly from Excel Table. I'd recommend always querying tables through PowerQuery first, to perform necessary transformation.

In PQ Editor, you can simply select AAA to DDD and unpivot columns.

However, it's not readily apparent what each number represent.

 

[_google]

Hi Chihiro,
Thanks for the reply, unfortunately am new to power query and powerpivot

Is it possible to upload file with changes as suggested by yourself so that later i can replicate same on my mail file( i tried several options ..for last two weeks).

1st place am not sure way i have created the data model structure is correct or not!!

Oh , "Zone" this are business markers against which Tolerance limit are set to arrive at business KPI .

I have managed my current file with nested Sumifs, but due to complex formula and source data size ...files goes to 15Mb resulting in slow calculation( hanging of file) ... felt standard excel is much easier then ..these power! stuff from Microsoft

AAA BBB CCC DDD
2 0 2 2
2 0 2 1
0 0 1 1

 

[Chihiro]

With your table, there really is no need for dimension table(s). Since all necessary info is contained within the table.

I'm still bit unclear as to what end result you are after. But see attached.

Table was first loaded to PowerQuery (Get & Transform), columns unpivoted and loaded to Data Model.

Instead of using calculated columns, used measures to aggregate data.

 

[_google]

Hi Chihiro,
thanks mate, file helped to give insight on how to manage with power query, have download power query on my system now.

"I'm still bit unclear as to what end result you are after." may be you were right to sense something was amiss.

On of the reason i have created data model was because each zone value(AAA,BBB..) has set tolerance limit to arrive at low and high.

Please find attached file, where i have shown my current excel sumifs method which works fine but only make file super slow for big data file i have.

if you can provide help on the attached file ( tweak) it will be great to further take learning and also fix problem at my end for efficient method. please help to attached file as it helps novice in ms power stuff

 

[Chihiro]

Ok, I see. But can't you simplify your criteria for low/high as...
Low <99.4, High >=99.4 etc?

Or could there be value lower than 0 for any zone?

Bit busy at work, but will look at it when I find time.

 

[_google]

Hi Chihiro
thanks and appreciate your support to pass on knowledge.

Yes there can be values in some cases below 0 thus business want to be specific to have value to start on 0. If it below zero it is outliers in those case due to errors hence ignored and addressed differently.

Understand, no problem you may reply when you have time from work priorities on current scenario shared ( worst case at least with file based on your suggestion).

may thanks

 

[Chihiro]

Here, see attached.

Added calculated column for Flag (this is required as you are doing Low/High designation in row context and not filter context).
[Flag]

= IF(AND(fTable[Value]>=RELATED(dZone[low-MIN]),fTable[Value]<RELATED(dZone[low-MAX])),"Low",IF(AND(fTable[Value]>=RELATED(dZone[High-MIN]),fTable[Value]<=RELATED(dZone[High-Max])),"High","NA"))

Then added 3 measures.

Lows:=CALCULATE(SUM(fTable[QTY]),FILTER(fTable,fTable[Flag]="Low"))

Highs:=CALCULATE(SUM(fTable[QTY]),Filter(fTable,fTable[Flag]="High"))

KPI:=[Lows]/([Lows]+[Highs])

KPI Status could be added, or just use Conditional Format on KPI column itself.

 

[Chihiro]

Come to think of it, it's probably better to use SWITCH function rather than using nested IFs.
[Flag]

=SWITCH(TRUE(),AND(fTable[Value]>=RELATED(dZone[low-MIN]),fTable[Value]<RELATED(dZone[low-MAX])),"Low",AND(fTable[Value]>=RELATED(dZone[High-MIN]),fTable[Value]<=RELATED(dZone[High-Max])),"High","NA")

 

[_google]

Hi Chihiro,

Many thank and may you be blessed wherever you are.

This will help me compared with what i did yesterday night, i finally managed using same file and based on your earlier inputs to arrive at least at the aggregation.

your solution looks perfect, many thanks again