Forum Discussion

monika_todor's avatar
monika_todor
Icon for Advocate II rankAdvocate II
2 years ago
Solved

MINX used in Matrix provides incorrect subtotal

Hi All,

I'm working on a report where the value I need to see on a subtotal level (for vreme) is the minimum value for the column in the matrix which in this example is -12.901.652, instead of -61.952.817 (which in this case is the summed value of the column).

The challenge is that the MIN value for each time is calculated based on 2 columns in 2 different data sources (which can't be merged): SUM(VkupnoPobaruva[Iznos]) - SUM(VkupnoDolguva[Iznos] hence I can't add a new column instead the measure.

Thank you in advance!

Best,

Monika

Need Help
  • monika_todor's avatar
    monika_todor
    2 years ago

    Thank you, I found a solution by inserting new data sources and changing the relationships in the model.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi monika_todor ,

    You mentioned that your two data sources could not be merged, I would like to inquire if there is some sort of one-to-one or one-to-many relationship between VkupnoPobaruva and VkupnoDolguva?
    If it exists, you can try this DAX to create a new column in one of the tables:

    Column = 
VAR Value2 = 
CALCULATE(
    MAX('VkupnoPobaruva'[Iznos]),
    FILTER(
        'VkupnoPobaruva',
        'VkupnoPobaruva'[time] = 'VkupnoDolguva'[time]
    )
)
RETURN
'VkupnoDolguva'[Iznos] - Value2

    Then put this column into the matrix:

    Best Regards,
    Dino Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • monika_todor's avatar
    monika_todor
    Icon for Advocate II rankAdvocate II
    2 years ago

    Thank you Dino for your detailed response, much apprecited!

    The thing is that the relationship model is much more complex. Both sources 'VkupnoPobaruva' and 'VkupnoDolguva' are related through DimVreme (for Time) and DimDatum (for date) which means that in none of the tables 'VkupnoPobaruva' and 'VkupnoDolguva' has all the dates and times from the other table, so adding a new column in one of these tables can't solve the problem as there may be rows missing.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi monika_todor ,

      Can you provide your data model? Replace your original data with test data so that you can avoid data security breaches. I can't reproduce complex models by creating them manually, thanks!

      Best Regards,
      Dino Tao

      • monika_todor's avatar
        monika_todor
        Icon for Advocate II rankAdvocate II
        2 years ago

        Thank you, I found a solution by inserting new data sources and changing the relationships in the model.