Issue with comments column_to be added in a table with several measures

[GiaD30]

Good morning everyone,

I have a situation and I really need some help/guidance.

Allow me to explain: I have a big table with several Ice cream factories, each factory has 1 or 2 ice cream type and each factory , for each ice cream type , has several flavours. Below you may see a sample of the table:

Now, I calculated several measures and I need to create a normal table in Power Bi and add the columns from the original table and also the measures. All the measures are calculated based on each factory, ice cream type and flavour.

The most important measures:

Revenue: Quantity* Price

Measure 1(this measure is calculated only for the flavour Vanilla, so it needs to be displayed only on the row where we see Vanilla) = Calculate(Sum(Table(Quantity)*0.7, FILTER(Table, Table(Ice cream flavour)= "Vanilla")

Measure 1_All = Calculate(Measure1, ALL(Table(Ice cream factory), Table(Ice cream type), Table(Ice cream Flavour))

Measure 2 = Calculate(DIVIDE(Revenue+Measure 1_ALL), Quantity), " "), Keepfilters(Table(Ice cream flavour)= "Chocolate"

Measure 2 is being calculated only for Chocolate, so it needs to be displayed on the Chocolate row only and it depends on Measure 1(based on Vanilla), that's why I have calculated a Measure 1 , using ALL.

So good so far, the problem starts when I want to add the the Comments column, where I have different comments for each ice cream flavour.

When I add the cooments, the Measure 2 value changes, meaning that it will not take into consideration Measure 1 value.

I tried to add to Measure 1_ALL formula also the Comments column, but I get an error.

is there a way to fix this? I really need to add the Comments column, along with all the Measures.

Thank you.

[danextian]

Hi @GiaD30

What result do you expect Measure1_ALL to return based on your sample data? If your goal is to return the value for 'vanilla' regardless of other row values—and only show it in the 'vanilla' row—try this:

Vanilla2 = 
IF (
    SELECTEDVALUE ( 'Table'[Ice cream flavour] ) = "vanilla",
    CALCULATE (
        SUM ( 'Table'[Quantity] ) * 0.7,
        ALLEXCEPT ( 'Table', 'Table'[Ice cream flavour] ),
        'Table'[Ice cream flavour] = "vanilla"
    )
)

[GiaD30]

@danextian  many thanks for your reply. So, the normal Measure 1, without ALL is based on Vanilla and I need to display it only in the row with Vanilla, which is correct. So, the Measure 1 is correct.

Now, the issue starts , for me, with Measure 2, because the calculation os Measure 2 includes Measure 1, but Measure 2 needs to be displayed only in the row with Chocolate. That's why I had to add also a Measure 1_ALL, which will not actually appear in the table, I needed only to calculate Measure 2.

Measure 1_All = Calculate(Measure1, ALL(Table(Ice cream factory), Table(Ice cream type), Table(Ice cream Flavour))

Measure 2 = Calculate(DIVIDE(Revenue+Measure 1_ALL), Quantity), " "), Keepfilters(Table(Ice cream flavour)= "Chocolate"

Now, all these measures are correct, BUT, the issue comes when I need to add the Comments column(different comments for each flavour).

Because, when I add the Comments into the table with my measures, the Measure 2 will not include into the calculation the Measure 1.

Example: in my pictures above, the Measure 2 is 6.05, but, once I add the Comments, the result is 5.

Measure 1= 10.5

Correct Measure 2 = (50+10.5)/10

when adding Comments, Measure 2 = 50/10

So, what I need is to fix this issue, to be able to add the Comments column and still have the correct Measure 2 calculated.

All the 

[v-kathullac · Accepted solution]

Hi @GiaD30 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

Thanks for the detailed explanation of your issue Can you try with the below dax

Measure 1_ALL =
CALCULATE(
[Measure 1],
REMOVEFILTERS('Table'[Ice cream flavour]),
REMOVEFILTERS('Table'[Ice cream type]),
REMOVEFILTERS('Table'[Ice cream factory]),
REMOVEFILTERS('Table'[Comments]))
This ensures Measure 1_ALL calculates globally, even when Comments are in the table.

Measure 2 =
CALCULATE(
DIVIDE([Revenue] + [Measure 1_ALL], [Quantity]),
KEEPFILTERS('Table'[Ice cream flavour] = "Chocolate")
)
This makes sure the calculation happens only on Chocolate, but still uses the correct full Measure 1_ALL

3) Show Measure 2 only in Chocolate rows (hide elsewhere):
Measure 2(visible only for Chocolate) =
IF(
SELECTEDVALUE('Table'[Ice cream flavour]) = "Chocolate",
CALCULATE(
DIVIDE([Revenue] + [Measure 1_ALL], [Quantity])
)
)

Regards,

Chaithanya.

[GiaD30]

@v-kathullac   Thank you so much, it worked 🙂

[Daniel29195]

hello @GiaD30 

in measure 1, add the column comments in the ALL function, so that you remove the filters on the comments also. 

Measure 1_All =

Calculate(

Measure1,

ALL(

Table(Ice cream factory),

Table(Ice cream type),

Table(Ice cream Flavour),

table[comments] -- add this 

)

)

hope this helps 

[GiaD30]

@Daniel29195  Thank you so much for the reply, I dis this, but when I add the comments column into the table, it is messing up the table and I get an error

[Daniel29195]

may i know what is the errror you are getting ?  @GiaD30 

[GiaD30]

@Daniel29195 , so, when I add the comments to my Measure All, I get this message: MdxScript(Model)(250, 247) Calculation error in Measure 2 ALL: Cannot convert value ' John: 34*44'  of type Text to Type True/False