Forum Discussion

MP275's avatar
MP275
Frequent Visitor
2 years ago
Solved

Hi All,

I am struggling to get the below solved using dax, could someone help plss   We have date and time dimensions with sales for every 15 minute intervals. I have a measure for sum of sales, I would li...
Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    MP275,

     

    Try a measure like this:

     

    Count Sales Over 40k =
VAR vSalesByDateAndArea =
    ADDCOLUMNS (
        SUMMARIZE ( FactTable, FactTable[Date], FactTable[Area] ),
        "@Sales", [Sum of Sales]
    )
VAR vResult =
    COUNTROWS ( FILTER ( vSalesByDateAndArea, [@Sales] > 40000 ) )
RETURN
    vResult
DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
2 years ago

MP275,

 

Try a measure like this:

 

Count Sales Over 40k =
VAR vSalesByDateAndArea =
    ADDCOLUMNS (
        SUMMARIZE ( FactTable, FactTable[Date], FactTable[Area] ),
        "@Sales", [Sum of Sales]
    )
VAR vResult =
    COUNTROWS ( FILTER ( vSalesByDateAndArea, [@Sales] > 40000 ) )
RETURN
    vResult
  • MP275's avatar
    MP275
    Frequent Visitor
    2 years ago

    Thanks alot DataInsights , this is working but it doesn't calculate for some of the dates as those sales for the dates are coming from a different fact table (based on a condition, if the sales from fact 1 is 0 then use sales from fact 2), I just realised that there are two fact tables. is there a way for this?

    (we are using a live model with limited access to the model).

    So the requirement is to check if there are any sales more than 40000 and if not then check in fact table 2 for that date / time / region, if there is then use the sales from facttable2. and count the rows. The existing measure for sum of sales captures the same logic (if sales blank in fact 1 then use values in fact 2)