Forum Discussion

mtownend's avatar
mtownend
New Member
9 years ago

Percentage at different Levels

Hi,

 

I have two tables one called Location and the other called Fact_Avail

 

The location table contains information as below.

 

RegionClusterStore
751011236
751013216
801014569
801017423
751027894
801029654

 

Fact_Avail

StoreASVLSV
12362144.1985
32162081.659
45692307.6368
74231822.7728
7894120421.7677
96543510.0697

 

I am wanting to create a calculated percentage so that if i select a region or clust or Store the percentage will calculate.

 

Thanks

 

 

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi, mtownend

     

    You can take my test as a reference. In this example, I calculated the percentage for column ASV, if I select 75 for region, the percentage should be (214+208+1204)/(214+208+230+182+1204+35) = 0.78.

     

    Formulas:

    ALLTotalASV = CALCULATE(SUM(Fact_Avail[ASV]),ALL(Fact_Avail))

SelectTotalASV = SUM(Fact_Avail[ASV]) 

Percentage = [SelectTotalASV]/[ALLTotalASV]

    Visualization:

     

     

    Best regards,

    Yuliana Gu

    • mtownend's avatar
      mtownend
      New Member
      9 years ago

      Hi

       

      I have tried the solution but get the same values for AllTotalASV as for the SelectTotalASV

       

      AllTotalASV = calculate(sum(Fact_AvailStoreItem[ActualSalesVolume]),All(Fact_AvailStoreItem))

       

      SelectTotalASV = sum(Fact_AvailStoreItem[ActualSalesVolume])

       

      Thanks

       

      Martin

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        9 years ago

        Hi, mtownend

         

        Please make sure that you created measures rather than calculated columns using above formulas.

         

        Thanks,
        Yuliana Gu