Forum Discussion
How can I combine formula in Data Model
Hi,
I am trying to combine data from Data Model into a Pivot Table , I have written some Measures but it hasn't shown the expect result.
I have added an image and the link of the Samplefile below
Hope for your help
Thank you
https://docs.google.com/spreadsheets/d/17vk5RKv2qUJIvD_5H-E0DRg_uO5xeoFw/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true
If build relationships as
your PivotTable gives practically desired result.
The only it's better to use
perc late A:=DIVIDE( [Total late], [Total ontime], BLANK() )instead of
perc late:=[Total late]/[Total ontime]As third parameters in DIVIDE you may use whatever you want instead of BLANK().
Another point we have blank for hub areas, that means not all areas appears in Table1 and Table2 are in Table3. But that's better to adjust Table3.
Please check in attached file.
14 Replies
That could be done by Power Query as in attached file. I guess all your tables are generated from data model. If so perhaps it'll be easier to generate required result directly in model and return into the sheet as Table or PivotTable.
Thank for your assistance
The Power Query shows correctly 100% the expect result.
How can we do it the same by Power Pivot because I want to create a pivot table from Power Pivot, that I can see details what I need.
Hope for your helpIt depends on what do you have in data model. If you could share the file with it and PivotTables you generated in the sample I could try.
- Alternatively, to create a formula that uses a function, click the “Design” tab in the data model window's Ribbon. Then click the “Insert Function” button in the “Calculations” button group to open the “Insert Function” dialog box. Then select a function in the listing in this dialog box
Hope this helpsThank you
Give a try to below formula. See sheet "harun24hr".
=LET(x,UNIQUE(F2:F40156),a,COUNTIFS(B2:B40156,x),b,COUNTIFS(E2:E40156,x),c,a/b,CHOOSE({1,2,3,4},x,a,b,c))you have given me a amazing formula to get correctly result. Actually, I want to create a pivot table from Power Pivot, that I can see details what I need.
Thank you
littlevillage Your file didn't include a DM with measures. Though it includes hidden sheets with numerous errors and circular references.
Nevertheless, see if the attached file does what you need.
Thank for your response
Actually, I want to get expect result directly from data ( column A:E)
I have created a pivot table from power pivot, but it returns the failed result
I have added an image and the link of the samplefile below that shows the expect result
Hope for your help
https://docs.google.com/spreadsheets/d/1Q5_5Uurc44ldWcopN4fE1hNA_9gdIweB/edit?usp=sharing&ouid=112310544159929632856&rtpof=true&sd=true
In you sample file "HUB are" 239F01 has 80 time in column B (Late Area). So, why your expected result is 72 for this 239F01 hub area?
because the pivot table counts with criteria in column C,
