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 All,
I have data in the following format.
LOC | Report Name | Channel | KPI | Period | Product | Data |
Spain | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 20788 |
Spain | Report 1 | CH 1 | CH 1 - % Media Used in Details | Jan-17 | Product1 | 53 |
UK | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 746 |
France | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 309 |
Italy | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 2983 |
Italy | Report 1 | CH 1 | CH 1 - % Media Used in Details | Jan-17 | Product1 | 4 |
Brazil | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 8820 |
Brazil | Report 1 | CH 1 | CH 1 - % Media Used in Details | Jan-17 | Product1 | 798 |
Mexico | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 8283 |
Mexico | Report 1 | CH 1 | CH 1 - % Media Used in Details | Jan-17 | Product1 | 2847 |
Turkey | Report 1 | CH 1 | CH 1 - Brand Details | Jan-17 | Product1 | 4563 |
Turkey | Report 1 | CH 1 | CH 1 - % Media Used in Details | Jan-17 | Product1 | 2360 |
Italy | Report 1 | CH 1 | CH 2 - Brand Details | Jan-17 | Product1 | 1 |
Brazil | Report 1 | CH 1 | CH 2 - Brand Details | Jan-17 | Product1 | 1 |
UK | Report 1 | CH 1 | CH 3 - Brand Details | Jan-17 | Product1 | 68 |
Turkey | Report 1 | CH 1 | CH 3 - Brand Details | Jan-17 | Product1 | 2 |
I need the output as follows;
CH 1 - Brand Details | 46492 |
CH 1 - % Media Used in Details | 13.04% |
where 'CH 1 - Brand Details' is a simple sum
but for 'CH 1 - % Media Used in Details' it should be the sum of 'CH 1 - % Media Used in Details' divided by sum of 'CH 1 - Brand Details'
I hope this make sense. Any help would be appreciated.
Kris
Solved! Go to Solution.
Hi @Anonymous
Add this Measure in your Table
Measure= IF ( HASONEVALUE ( Table1[KPI] ), IF ( VALUES ( Table1[KPI] ) = "CH 1 - Brand Details", SUM ( Table1[Data] ), IF ( VALUES ( Table1[KPI] ) = "CH 1 - % Media Used in Details", ROUND ( ( SUM ( Table1[Data] ) / CALCULATE ( SUM ( Table1[Data] ), Table1[KPI] = "CH 1 - Brand Details" ) * 100 ), 2 ) ) ) )
Hi @Anonymous
Add this Measure in your Table
Measure= IF ( HASONEVALUE ( Table1[KPI] ), IF ( VALUES ( Table1[KPI] ) = "CH 1 - Brand Details", SUM ( Table1[Data] ), IF ( VALUES ( Table1[KPI] ) = "CH 1 - % Media Used in Details", ROUND ( ( SUM ( Table1[Data] ) / CALCULATE ( SUM ( Table1[Data] ), Table1[KPI] = "CH 1 - Brand Details" ) * 100 ), 2 ) ) ) )
Hi Zubair,
Thanks for the reply, it works well. How can I make it dynamic, for example, if I have more than 1 item to do these kind of calculations ? Is it possible by using a mapping table where I can put the items and do the calculations?
Kris
Hi @Anonymous
Yes you can make them dynamic. Just Create a separate table with items you want.
Insert them as slicer.
Then you can replace the hardcoded "CH 1 - Brand Details" and "CH 1 - % Media Used in Details"
with VALUES (SlicerTable[Column])
Sorry for the late reply.
Thanks for the help. I'll try it.
Kris
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |