Forum Discussion
Measure to calculate
To create the measure you need in Power BI, you want to count the number of outlets that have contracts regardless of sales by target product or other products. Here is a DAX approach:
Assuming your table name is SalesData:
Number of contracted outlets (regardless of product sales):
ContractedOutlets =CALCULATE( DISTINCTCOUNT(SalesData[OutletID]),SalesData[Contract] = "y" )Number of outlets with sales on selected dates for target products only:
Make sure you have a Date slicer or filter that limits SalesDate to your 4 selected dates.
OutletsWithSales_TargetProduct =CALCULATE( DISTINCTCOUNT(SalesData[OutletID]), SalesData[TargetProductFlag] = "y", SalesData[SalesDate] IN VALUES(SelectedDates[Date]) // Assuming SelectedDates is your date filter table )Create a table visual with Area, Target Product Flag (filter to "y"), and these two measures.
This way:
Contracted outlets count is independent of sales/product.
Sales count is filtered on target products and selected dates.
Area is in rows for grouping.
If your date selection is via slicer/filter, the second measure automatically respects that.
This approach will give you correct counts per Area as needed.
Super UserHi laszloeszter
Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Please read this as well https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi Dane,
Unfortunately i can not upload data , but hope this helps:
tried to bring all data into one query, i can not filter the data in the query as the rest of the data is needed for other visuals.
originally i have 3 queries:
Customers ( around 5000), columns are:
Outlet ID, Area, Contract
Sales ( has data for the 5000 outlets on product, day level) with columns:
Outlet ID, Sales Date, Product Name, Sales Volume
Product Mapping ( for all products)
Product name, category, bonus flag
Customers and sales have a one to many relationship, and sales and product mapping have a many to one
What i want to achieve, that for Area one in customer table there are 70 customers, but only 56 contracted.
In sales, these 56 have sold from most of the product , but not from all of them.
In my table i need to see the Area, the product list that have a bonus flag in the product mapping table, the number of contracted outlets ( so this should be 56 no matter if they sold from that product or not), and on top of these i will need the number of these outlets who actually sold that product on 4 selected dates.
thanks