Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculated measures not working correctly with calculated table

Hi everybody, 

 

I have to create a price tiering in Power BI something like the picture below and the range can be variable anytime by the user. 

 

For variability every product need a constant indicator. For this indicator I use some calculated measure. Now I have this to table. The product column is not empty, it contains uniqe values but I can't share the content.

This two table contans the same calculated measures. 

Indicator = IF(DIVIDE([Price/kg TY];[MAT_woProduct_TY];1)*100=BLANK();
100;
DIVIDE([Price/kg TY];[MAT_woProduct_TY];1)*100)
Price/kg TY = DIVIDE([MAT_Values_TY];[MAT_Volume_TY];0)
MAT_woProduct_TY = CALCULATE(DIVIDE([MAT_Values_TY];[MAT_Volume_TY];0);ALL('SALES VALUE'[PRODUCT];'SALES VALUE'[MANUFACTURER])) (this measure is the "MAT" in the tables. 
MAT_Values_TY = CALCULATE(sum('SALES VALUE'[VALUES]);DATESINPERIOD('Calendar'[Dates];MAX('Calendar'[Dates]);-1;YEAR);'SALES VALUE'[FACTS]="SALES VALUE")
MAT_Volume_TY = CALCULATE(SUM('SALES VALUE'[VALUES]);DATESINPERIOD('Calendar'[Dates];MAX('Calendar'[Dates]);-1;YEAR);'SALES VALUE'[FACTS]="SALES VOLUME")
Tiers TY =
IF([Indicator TY]<'Tier-Economy'[T.ier-Economy];"Economy";
IF([Indicator TY]<'Tier-Standard'[Tier-Standard Value];"Standard";
IF([Indicator TY]<'Tier-Premium'[Tier-Premium Value];"Premium";
"Super Premium")))
 
In the left side the slicers and cards are parameters.
In the bottom table the segment and product column come from "Sales Value" table and the rest of the table are calculated measures. Here the "Tiers TY" is working correctly. 
The top table is a calculated table:
Tiers = ADDCOLUMNS(SUMMARIZECOLUMNS(
'SALES VALUE'[SEGMENT];
'SALES VALUE'[PRODUCT];
"Price/kg TY";[Price/kg TY];
"MAT";[MAT_woProduct_TY];
"Indicator";[Indicator TY]);
"Tier";IF([Indicator]<'Tier-Economy'[Tier-Economy];"Economy";
IF([Indicator]<'Tier-Standard'[Tier-Standard Value];"Standard";
IF([Indicator]<'Tier-Premium'[Tier-Premium Value];"Premium";
"Super Premium"))))
 

 In the two table all data is the same, but the "Tiers TY" calculated measure is show wrong data and I have no idea why. 

 

Thanks a lot. 🙂

 

 

 

 

Need Help

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    The measure is dynamic but the calculated tables are computed during the database processing and then stored in the model. So In your scenario, when the calculated table is generated, it will use the default value of parameter. We suggest to use the measure if you want the tiers can be dynamic by the selected value of parameter.

     

     


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi v-lid-msft 

       

      Thank you for your answer but this solution is not working in my scenario. 

       

      I can't use calculated measure, because in the end of the tier calculation I have to count how many economy, standard, premium and super premium product each manufacturer have. 

       

      It's necessary because i would like to show the distribution in a graph. 

      Something like this:

       

      Thanks, 

      Anett 

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Sorry for our late reply, We can create a separation table as the Legend:s

         

        TiersTable = {"Economy","Standard","Premium","Super Premium"}

         

        Then we can use another measure as the value to count the number to used in the barchart:

         

        Measure = 
COUNTROWS (
    FILTER (
        SUMMARIZE (
            'SALES VALUE',
            'SALES VALUE'[SEGMENT],
            'SALES VALUE'[PRODUCT],
            "TYS", [Tiers TY]
        ),
        [TYS] IN FILTERS ( 'TiersTable'[Value] )
    )
)

         


        Best regards,