Forum Discussion

laszloeszter's avatar
laszloeszter
Regular Visitor
9 months ago
Solved

Measure to calculate

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!

Need Help
  • Shubham_rai955's avatar
    Shubham_rai955
    9 months ago

    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.

          •  

          •  

4 Replies

  • Shubham_rai955's avatar
    Shubham_rai955
    Icon for Super User rankSuper User
    9 months ago

    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.

          •  

          •  

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    9 months ago

    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

    • laszloeszter's avatar
      laszloeszter
      Regular Visitor
      9 months ago

      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