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.   Region Cluster Store 75 101 1236 75 101 3216 ...
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