Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 6 products, and want to asigne reveneu of one of them (product A) to three others(Product B, C, D). The asignment should be based on portion of reveneu of each of the three products. Meaning if 30% of total reveneu of B, C, D is for B, so 30% of total reveneu of A should be asigned to B.
And there is one table which include all transactions with amount and product names.
What I did, calculating a masure as:
Ratio_for_Devide = 1+(CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product] = "A")/(CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product]= "B")+CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product] = "C")+CALCULATE(sum(Table[Normalized CAD]),ALLSELECTED(Table),Table[VP Product] = "D")))
Then I multipl this ration in all lines in the table in a new column as:
Product CAD = if(Table[VP Product]="B",(Table[Normalized CAD]*[Ratio_for_Devide]),if(Table[VP Product]="C", (Table[Normalized CAD]*[Ratio_for_Devide]),if(Table[VP Product]="D", (Table[Normalized CAD]*[Ratio_for_Devide]),if(Table[VP Product]="A",0,Table[Normalized CAD]))))
Now, the value of the ratio is correct but when line by line it is multipe to amount, it is less than actual ratio?
How can I fix it?
Your requirement is not clear for me, would you mind posting some sample data and expected output?
Or even better you could upload the pbix file to any web storage, such as Onedrive, Dropbox and share a link.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
2 | |
2 | |
1 | |
1 |