Forum Discussion

alexei7's avatar
alexei7
Continued Contributor
6 years ago

Total Incorrect for weighted calculation - DAX help

Hi everyone,   I'm struggling with DAX and hoping someone here can help.   My data is a table with a row for each sale, split by date and country. I have another table which contains the number ...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi alexei7 

Create a measure

Measure = IF(ISINSCOPE(Sales[SalesDateMonth]),[Adjusted Sales],SUMX(ALLSELECTED(Sales),[Adjusted Sales]))

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • alexei7's avatar
    alexei7
    Continued Contributor
    6 years ago

    Hi Maggie,

    Thanks very much for your help.

    This does work in that specific visual, however I also want to use this metric in a matrix visual with the date.
    Equally I noticed that the metric only worked when the slicer is being used, i want this to work all the time.

     

    The below is the kind of chart where i want this metric to also work:

     

     

     

     

     

     

     

    Let me know if you have any questions where I can clarify.

     

    Thanks

    Alex

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      6 years ago

      Hi  alexei7 

      Do you want to column total of matrix to show correct?

      If so, create a measure

      Measure 2 =
IF (
    ISINSCOPE ( Sheet1[date] ),
    [Measure],
    SUMX (
        FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[country] = MAX ( Sheet1[country] ) ),
        [Measure]
    )
)

      Best Regards
      Maggie
      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
      • alexei7's avatar
        alexei7
        Continued Contributor
        6 years ago

        Hi Maggie,

         

        Thanks for your time in looking into this.

         

        I don't quite understand your solution, and it doesn't correlate to my example, so I can't translate it, or make it work.

        e.g. my example has two connected tables

         

        The pbix model with dummy data is attached here:
        https://mega.nz/#!rwNBTSSY!cK0EAKschH1tAcXspNRdF4rwCY9YoOXebyREhmXVamI