Need help with creating a measure

[Anonymous]

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

[Zubair_Muhammad · Accepted 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
            )
        )
    )
)

[Anonymous]

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

[Zubair_Muhammad]

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])

[Anonymous]

Sorry for the late reply.

Thanks for the help. I'll try it.

Kris