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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ahmetturetmis
Frequent Visitor

DAX measure Suggestion

Hello All,

 

I would like to ask a measure formula because I couldn't write properly. I miss some little details but I can't see where. 

 

You can see the table below. I add some Letters on top the columns in order to make it easier to explain what I plan to.  I dont have to show all the columns but I need to see the results of each calculation thats why I increased the column amount.

 

Annotation 2020-08-06 123431.jpg

 

 

Only A and C columns are not measures rest of them are measure. 

 

In summary  I try to distribute Mh values of some CBS Id (From CBS)  to Other Selected CBS Id (Focus CBS) with a dynamic weight. I have problem to calculate this dynamic weight.

 

This dynamic weight should be calculated by the amount of the Focus CBS Quantities for example between the green lines:

 

% Focus weight is given by me in terms of the importance of the focus activity.

 

Weight percentage is calculated by the quantitty of Focus CBS / total Focused CBS 

 

Last factor Nominator is % Focus Weight * Weight percentage

 

Last Factor Denominated is the place that I cannot calculate correctly. For example It should be the total of the 0.09 + 0.13 + 0.07 = 0.29 then I will divide 0.09/0.29 and get the last Percentage.

 

Thanks a lot in advance for your help !!

 

 

 

 

1 ACCEPTED SOLUTION

Hi @ahmetturetmis,

You can use following calculate table formula to create a new table that summary raw table value, it should dynamically change based on the raw 'calculation table':

summary =
GROUPBY (
    SUMMARIZE (
        T2,
        [FROM CBS],
        [FOCUS CBS],
        [MH],
        "Rate", [Final Weightage] * [MH]
    ),
    [FOCUS CBS],
    "Total", SUMX ( CURRENTGROUP (), [Rate] )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
ahmetturetmis
Frequent Visitor

I've prepared a dummy table :

 

Data table this is changing everyday.

 

DateCBSQuantityMH
01-09-20A112
01-09-20B215
01-09-20C318
02-09-20D421
03-09-20E524
02-09-20F627
02-09-20G730
02-09-20H833
  Total180

 

This is a Importance matrix

 

 ABE
A1  
B 1 
C2 1
D11 
E  1
F11 
G  1
H 1 

 

This is the Calculation Table

 

FROM CBSFOCUS CBSFROM CBS MHFROM CBS QTYQTY WEIGHTIMPORTANCEIMPORTANCE WEIGHTQTY WEIGH X IMPORTANCE WEIGHTTotals of CombinationFinal WeightageMHDISTRIBUTED MH
AA121100%1100%100%100%100%1212.00
BB152100%1100%100%100%100%1515.00
CA18117%267%11%39%29%185.14
CE18583%133%28%39%71%1812.86
DA211100%150%50%64%78%2116.33
DB21229%150%14%64%22%214.67
EE24571%1100%71%71%100%2424.00
FA27133%150%17%50%33%279.00
FB27267%150%33%50%67%2718.00
GE305100%1100%100%100%100%3030.00
HB332100%1100%100%100%100%3333.00
          Total180

 

 

This is the Summary of Focus  CBS

 

FOCUS CBSDISTRIBUTED MH
A42.48
B70.67
E66.86
Total180

 

This calculations are changing with the new quantity and mh values added.

 

 

Hi @ahmetturetmis,

You can use following calculate table formula to create a new table that summary raw table value, it should dynamically change based on the raw 'calculation table':

summary =
GROUPBY (
    SUMMARIZE (
        T2,
        [FROM CBS],
        [FOCUS CBS],
        [MH],
        "Rate", [Final Weightage] * [MH]
    ),
    [FOCUS CBS],
    "Total", SUMX ( CURRENTGROUP (), [Rate] )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
v-shex-msft
Community Support
Community Support

Hi @ahmetturetmis,

So you mean you want to create a measure that can get the diff based on selection ranges and current category type? 

If this is a case, you can write a measure with a variable to extract the special ranges and summarize that ranges with your measure formulas based on the index fields. (you can't directly use two text values to find out the records between the selection records)

measure =
VAR _start =
    MIN ( table[Index] )
VAR _end =
    MAX ( selector[Index] )
VAR currLFD =
    CALCULATE (
        [Last Factor Denominated],
        ALLSELECTED ( table ),
        VALUES ( table[From Cbs] )
    )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( table ), [Index] >= _start && [Index] <= _end ),
        [From Cbs],
        "LFD", [Last Factor Denominated]
    )
RETURN
    currLFD / SUMX ( summary, [LFD] )

In addition, it will help if you share some dummy data with your formulas for future testing.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@ahmetturetmis , can you share actual formula

Can you share sample data and sample output in table format?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.