Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago

Save a total to a variable

Nice day

I have this problem, you want to save the total of the column "Vta x M2" (1.433) in a single fixed variable to use it for a color conditional.

Vta x M2 is the result of dividing "Sales" by "M2" (Square Meters) of each store. Total sales 1,433 soles through all stores. I want to save that result to 1 variable.

Use this formula but it doesn't work:

M2 value =
VAR VM2 = [Sales] / Sum(Dim_Tiendas[Meters)
VAR VALM2 = YES ([Vta x M2]> VM2, 1 , 0 )
RETURN
VALM2


Thank you in advance,

I remain attentive to your help,

Thanks a lot

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Are you planning to use a measure or a calculated column? Is the color impacted by users interacting with the report or is it fixed ?

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      1 year ago

      It is a measure.

      The color will give according to the conditional.

      I did tests and I think that with calculate, it would work for me:

      CALCULATE ([Vta x M2],(ALLSELECTED(Stores)))

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    Hi Syndicate_Admin ,

    Please try to update your DAX to look like this:

    M2 value = 
VAR TotalVtaM2 = SUMX(Dim_Tiendas, [Sales] / Dim_Tiendas[Meters])  -- Calculate the total Vta x M2 for all stores
VAR VM2 = [Sales] / SUM(Dim_Tiendas[Meters])  -- Calculate Vta x M2 for each store
VAR VALM2 = IF(VM2 > TotalVtaM2, 1, 0)  -- Conditional check based on TotalVtaM2
RETURN
VALM2