Forum Discussion

smiler2021's avatar
smiler2021
Regular Visitor
4 years ago
Solved

monthly split per category

Hi,

can somebody help with the following issue:

 

I have a table of budget values for full year per product and

I have a table of % split into months per product - splits are different per product.

I have managed to do a dax formula that shows per product correct values.

But the total is incorrect, calculation is not suming up outcome of each product, it is calulating sum of % and Budget value and therefore doubling values.

 

How can I do a dax formula that is do this total also correct?

 

Thanks!!!

 

BR,

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi smiler2021 ,

    Sorry for delay. I created a sample pbix file(see attachment) for you base on your provided sample data, please check whether that is what you want. You can create two measures as below to get the budget value per product and month:

    Measure = 
VAR _selproduct =
    SELECTEDVALUE ( 'Budget'[Product] )
VAR _selmonth =
    SELECTEDVALUE ( 'Split'[Month] )
VAR _budget =
    CALCULATE (
        MAX ( 'Budget'[Budget] ),
        FILTER ( 'Budget', 'Budget'[Product] = _selproduct )
    )
VAR _percentmonth =
    CALCULATE (
        MAX ( 'Split'[% of month] ),
        FILTER ( 'Split', 'Split'[Product] = _selproduct && 'Split'[Month] = _selmonth )
    )
RETURN
    _percentmonth * _budget
    Budget value per product and month = 
SUMX (
    VALUES ( 'Budget'[Product] ),
    SUMX ( VALUES ( 'Split'[Month] ), [Measure] )
)

    If the above one is not your expected result, please provide more sample data with Text format and your expected result with more details(include calculation logic, special examples etct.). Thank you.

    Best Regards

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi smiler2021 ,

    Could you please provide some raw data from the relevant tables used in your matrix? Are the fields Period, Budget and Product from the same table? If not, are there any relationships created between the tables? Please provide the relevant relationship information(cardinality, direction, based filed etc.) if a relationship was created. What is the calculation logic to get the budget of each product? What is the final result you want? Is it the budget of each product as a percentage of the budget of all products? Could you please provide a specific example to illustrate the calculation logic and your expected result(correct values and visual settings etc.)? Thank you.

    Sample data:

    Col1 ... Colx
    xx xx xx
    xx xx xx

    Calculation logic:

    Budget%=Budget per product/Total Budget?

    Expected result:??

     

    Best Regards

    • smiler2021's avatar
      smiler2021
      Regular Visitor
      4 years ago

      Hi,

       

      thanks for your response, see my sample data and expected outcome below.

      There is a relation between the tables via product.

      The main issue is, that if I select both products I receive wrong values (see table in first link).

      Hope this is now better understandable.

       

      Thanks and BR,

      Smiler2021

       

      I have tried to put the sample data exactly like discribed in this link: How to provide sample data in the Power BI Forum - Microsoft Power BI Community

      But unfortunately data is always messing up when I post it in table.

      Therefore I again have to post a screeshot - sorry for that.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi smiler2021 ,

        Sorry for delay. I created a sample pbix file(see attachment) for you base on your provided sample data, please check whether that is what you want. You can create two measures as below to get the budget value per product and month:

        Measure = 
VAR _selproduct =
    SELECTEDVALUE ( 'Budget'[Product] )
VAR _selmonth =
    SELECTEDVALUE ( 'Split'[Month] )
VAR _budget =
    CALCULATE (
        MAX ( 'Budget'[Budget] ),
        FILTER ( 'Budget', 'Budget'[Product] = _selproduct )
    )
VAR _percentmonth =
    CALCULATE (
        MAX ( 'Split'[% of month] ),
        FILTER ( 'Split', 'Split'[Product] = _selproduct && 'Split'[Month] = _selmonth )
    )
RETURN
    _percentmonth * _budget
        Budget value per product and month = 
SUMX (
    VALUES ( 'Budget'[Product] ),
    SUMX ( VALUES ( 'Split'[Month] ), [Measure] )
)

        If the above one is not your expected result, please provide more sample data with Text format and your expected result with more details(include calculation logic, special examples etct.). Thank you.

        Best Regards