Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Measure totals do not return expected values

Hi All,

 

I've a simple measure with 2 variables that calculate the sum/difference of columns from multiple tables and result is set to return min of these 2 variables, It displyed correctly in the table but the total does not show the expected value. 

 

Current output : Min (A+B, C+D) i.e., Min (6,8) = 6

Expected output : Sum of MinResult (1+1) = 2

 

I need the sum of the final MinResult as in table below

 

TIA, for your help

 

For Ex: 

 

 

# MinResult = 
VAR A+B = SUM(Table1[A])+SUM('Table2'[B])
VAR C+D = SUM(Table1[C])+SUM('Table2'[D])
VAR Result = 
    (
        MIN ( (A+B), (C+D))
    )
    Return Result

 

 

ABCDA+BC+DPseudo valueCountryMin Result (A+B , C+D)
012315US1US1
340171US2US1

 

@Ashish_Mathur - could you help plz, Thank You!

 

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario.

b1.png

 

You may create three measures as follows.

AB = SUM('Table'[A])+SUM('Table'[B])

CD = SUM('Table'[C])+SUM('Table'[D])

MinResult = 
SUMX(
    'Table',
    CALCULATE(MIN([AB],[CD]))
)

 

Result:

b3.png

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

8 REPLIES 8
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

 

Best Regards

Allan

v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario.

b1.png

 

You may create three measures as follows.

AB = SUM('Table'[A])+SUM('Table'[B])

CD = SUM('Table'[C])+SUM('Table'[D])

MinResult = 
SUMX(
    'Table',
    CALCULATE(MIN([AB],[CD]))
)

 

Result:

b3.png

 

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

Thank you!!

amitchandak
Super User
Super User

The information you have provided is not making the problem clear to me. Can you please explain with an example.

refer if you looking for :https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Appreciate your Kudos.

 

Anonymous
Not applicable

I'll try to explain the issue better this time

 

I've a measure to return min of two variables. So, let's say for a given country, i have 2 entries, 1 for each state as below. As expected, measure returns the min values for each state (see below). Now, when i show the total value for column - 'Min value' it is showing 5 instead i.e., it is adding the values for B (5) and comparing it with A (6) and returning the minimum value of the total

 

ABStateCountryMin value of A,B 
12TxUS1 
53CaUS3 
   Total5 

 

 

 

Try like

sumx(summarize(table,table[state],table[country], "_min",min(table[A],table[B])),[_min])

 

Anonymous
Not applicable

It is not working.. i tried with sample data and returns the following error

 

 

Anonymous
Not applicable

i couldn't upload the pic. I am using the same table as mentioned above

"The syntax for 'Table' is incorrect. (DAX(sumx(summarize (Table,'Table'[State],Table[Country], "_min",min(Table[A],Table[B])) ,[_min])))."

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.