I am trying to replicate my excel calculations in Powerbi. Sheet 3 in https://drive.google.com/file/d/1c607JpoZPCWXfK9exJaTjGo4kU9oWdyD/view?usp=sharing and sheet 2 in https://drive.google.com/file/d/1E-k77iw7N58FFqX3lf2CBEDBoGFKUpq_/view?usp=sharing i devide to get desired results. I would like the same in Powerbi.
To get the desired tables and column name changes i use this sql code:
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’
Problem is, i am not getting these sales data in my powerbi model when using the above sql script, as indicated here: https://drive.google.com/file/d/1E-k77iw7N58FFqX3lf2CBEDBoGFKUpq_/view?usp=sharing
Some how my data for the app category disapear.
What am i doing wrong?
PBIX file with SQL code use https://drive.google.com/file/d/186vsjbqclSXB4m5LGwdp3agMQ6-63LDs/view?usp=sharing
PBIX when i pull in the tables manually :https://drive.google.com/file/d/1HCoYlPrhBQKuvWb-TpOBbDBEnv0BV0Sj/view?usp=sharing
