Percentage at different Levels

[mtownend]

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
80	101	4569
80	101	7423
75	102	7894
80	102	9654

Fact_Avail

Store	ASV	LSV
1236	214	4.1985
3216	208	1.659
4569	230	7.6368
7423	182	2.7728
7894	1204	21.7677
9654	35	10.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

[v-yulgu-msft]

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]

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]

Hi, @mtownend

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

Thanks,
Yuliana Gu