Measure to calculate

[laszloeszter]

Hi All, 

I have a table that has Area, outlet id, contract ( y/n), product name, target procuct flag (y/n) , sales date and sales volume columns.
I need to create a table that has the columns: Area, target products only, number of outlets that have contracts, and number of outlets that had sales on 4 selected dates.
my issue is that not all outlets have sales from all products ( but all sold from at least one of the products).

i need a measure that calculates the number of contracted outlets no matter if they sold from the product or not.

thanks!

[Shubham_rai955 · Accepted solution]

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:

1. Number of contracted outlets (regardless of product sales):

    

   ContractedOutlets =

   CALCULATE( DISTINCTCOUNT(SalesData[OutletID]),

   SalesData[Contract] = "y" )

   1. 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 )

      1. 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.

[danextian]

Hi @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/1447... 

[laszloeszter]

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

[Praful_Potphode]

Hi @laszloeszter 
without details its difficult to come up with measure.please share more information on data model,input and output.Your measure may look like below:

Contracted Outlets = 
CALCULATE(
    DISTINCTCOUNT('Sales'[outlet id]),
    'Sales'[contract] = "Y",
    ALLEXCEPT('Sales', 'Sales'[Area])
)

This will change if your data model is different.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful