cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Field Parameter dynamic measure

Goog morning to all,

I have a matrix with a field parameter in rows and revenues in values.

I need to have a measure that calculate the "% on grand total" and the "cumulated %", based on the dimension selected in the fiel parameter.

Can anyone help me solfing the two DAX measures??

Luca

1 ACCEPTED SOLUTION
Community Support

Hi @L_G ,

``% on grand total = DIVIDE([Revenue],CALCULATE([Revenue],ALLEXCEPT('Table','Table'[Year])))``
``````cumulated % =
VAR _all_year = CALCULATE([Revenue],ALLEXCEPT('Table','Table'[Year]))
VAR _select_paramerter = MAX('Parameter'[Parameter])
VAR _country = CALCULATE([Revenue],'Table'[Country]<=MAX('Table'[Country]))
VAR _product = CALCULATE([Revenue],'Table'[Product]<=MAX('Table'[Product]))
VAR _month = CALCULATE([Revenue],'Table'[Month Number]<=MAX('Table'[Month Number]))
VAR _result =
SWITCH(
_select_paramerter,
"Country",DIVIDE(_country,_all_year),
"Product",DIVIDE(_product,_all_year),
"Month Number",DIVIDE(_month,_all_year)
)
RETURN
_result``````

Output:

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

3 REPLIES 3
Community Support

Hi @L_G ,

``% on grand total = DIVIDE([Revenue],CALCULATE([Revenue],ALLEXCEPT('Table','Table'[Year])))``
``````cumulated % =
VAR _all_year = CALCULATE([Revenue],ALLEXCEPT('Table','Table'[Year]))
VAR _select_paramerter = MAX('Parameter'[Parameter])
VAR _country = CALCULATE([Revenue],'Table'[Country]<=MAX('Table'[Country]))
VAR _product = CALCULATE([Revenue],'Table'[Product]<=MAX('Table'[Product]))
VAR _month = CALCULATE([Revenue],'Table'[Month Number]<=MAX('Table'[Month Number]))
VAR _result =
SWITCH(
_select_paramerter,
"Country",DIVIDE(_country,_all_year),
"Product",DIVIDE(_product,_all_year),
"Month Number",DIVIDE(_month,_all_year)
)
RETURN
_result``````

Output:

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Frequent Visitor

It works perfectly, thank you so much !!!

Super User

Hi @L_G

Can you please check below two DAX

``````% On Grand Total :=
DIVIDE(
SUM('Table'[SaleValue]),
CALCULATE(
SUM('Table'[SaleValue]),
ALL('Table')
)
)``````

``````Cumulated % Of Grand Total :=
DIVIDE(
CALCULATE(
SUM('Table'[SaleValue]),
FILTER(
ALL('Table'),
'Table'[Product] <= EARLIER('Table'[Product])
)
),
CALCULATE(
SUM('Table'[SaleValue]),
ALL('Table')
)
)``````

If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors