topic Re: Calculating/Dividing Two Pivot Tables in DAX Commands and Tips

Calculating/Dividing Two Pivot Tables

Yrstruly2021 — Wed, 24 Mar 2021 18:08:24 GMT

I am using excel to create pivot tables and than divide the tableIs to get desired outcome. See: https://drive.google.com/file/d/1jL4vNdBxAi0LPigy3iaQ-iGFzHiMPaBA/view?usp=sharing and https://drive.google.com/file/d/1dwPDuuOju7nbLyDVa5njvLObCqviJa3Z/view?usp=sharing

These questions: https://drive.google.com/file/d/1dwPDuuOju7nbLyDVa5njvLObCqviJa3Z/view?usp=sharing

i am answering in Excel currently. I would like to answer them in Powerbi. Please assist?

SQL i am using in Powerbi, to pull in the desired tables:

# App Filter Categories
select a.label as 'App Category'
, b.label as 'Menu'
, c.product_uid as 'Product UID'
from app_menu_filters a
left join app_menu_filters b on b.parent_uid=a.uid
left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid
where a.status='A' and a.level=1
order by 1,2,3;

# Sales and Product Data
SELECT date(o.created_datetime) as Date, od.product_uid as 'Product UID',
p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as 'Unit of Measure',
ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as 'Units Sold',
ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as 'Sales Volume',
ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as 'Sales Value'
FROM order_detail od
left join `order` o on od.order_uid=o.uid
left join product p on od.product_uid=p.uid
where o.status in ('D',1,2,3,4,5)
and not od.label ='Plastic Bag'
and date(o.created_datetime) >= '2020-04-01'

Please see data in access: https://drive.google.com/file/d/1zqSbAvESHf5WUw4U-OKXjL_9E8NbJTfd/view?usp=sharing

Please see data in excel: https://drive.google.com/drive/folders/1jUFHVlGZsHNdE6H0TuB2FI6c19Up52eR?usp=sharing

Re: Calculating/Dividing Two Pivot Tables

Anonymous — Tue, 06 Apr 2021 10:52:29 GMT

Hello Yrstruly2021 ,
Goggle Drive link is asking for access.
Can you share it properly?

Re: Calculating/Dividing Two Pivot Tables

Yrstruly2021 — Tue, 06 Apr 2021 22:11:03 GMT

https://drive.google.com/file/d/186vsjbqclSXB4m5LGwdp3agMQ6-63LDs/view?usp=sharing

Re: Calculating/Dividing Two Pivot Tables

Anonymous — Wed, 07 Apr 2021 04:46:05 GMT

Hello Yrstruly2021
Please make an active relationship and keep the cross filter direction to "Single". Sheet 3 should filter Sheet 1.
If you didn't get as expected then please share the expected output.
Thanks.

Re: Calculating/Dividing Two Pivot Tables

Yrstruly2021 — Sun, 16 May 2021 10:05:43 GMT

Please see PBIX file. I need to calculate the two, app category and manufacturers.

https://drive.google.com/file/d/1Bb_UPIws52ZcY_SpcsLXcEJMdZfp5t7k/view?usp=sharing

Re: Calculating/Dividing Two Pivot Tables

Yrstruly2021 — Sun, 16 May 2021 10:52:12 GMT

Thank you. It should be working now.

Re: Calculating/Dividing Two Pivot Tables

Yrstruly2021 — Wed, 19 May 2021 10:25:22 GMT

It is active within the pbix.

Re: Calculating/Dividing Two Pivot Tables

Yrstruly2021 — Sun, 23 May 2021 17:08:06 GMT

I believe the relationships are active?