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 produc...
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

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