Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Need help with creating a measure

Hi All,

 

I have data in the following format. 

LOCReport NameChannelKPIPeriodProductData
SpainReport 1CH 1CH 1 - Brand DetailsJan-17Product120788
SpainReport 1CH 1CH 1 - % Media Used in DetailsJan-17Product153
UKReport 1CH 1CH 1 - Brand DetailsJan-17Product1746
FranceReport 1CH 1CH 1 - Brand DetailsJan-17Product1309
ItalyReport 1CH 1CH 1 - Brand DetailsJan-17Product12983
ItalyReport 1CH 1CH 1 - % Media Used in DetailsJan-17Product14
BrazilReport 1CH 1CH 1 - Brand DetailsJan-17Product18820
BrazilReport 1CH 1CH 1 - % Media Used in DetailsJan-17Product1798
MexicoReport 1CH 1CH 1 - Brand DetailsJan-17Product18283
MexicoReport 1CH 1CH 1 - % Media Used in DetailsJan-17Product12847
TurkeyReport 1CH 1CH 1 - Brand DetailsJan-17Product14563
TurkeyReport 1CH 1CH 1 - % Media Used in DetailsJan-17Product12360
ItalyReport 1CH 1CH 2 - Brand DetailsJan-17Product11
BrazilReport 1CH 1CH 2 - Brand DetailsJan-17Product11
UKReport 1CH 1CH 3 - Brand DetailsJan-17Product168
TurkeyReport 1CH 1CH 3 - Brand DetailsJan-17Product12

 

I need the output as follows;

 

CH 1 - Brand Details46492
CH 1 - % Media Used in Details13.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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

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

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

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