Calculated measures not working correctly with calculated table

[Anonymous]

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. 🙂

[v-lid-msft]

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]

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]

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,

[v-lid-msft]

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,

[v-lid-msft]

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,