Forum Discussion
Please Help - Incorrect Matrix Row Total
Hello,
I have an issue with row total on my matrix, this is my mesure. The goal is to divide the total quantity which is generally equal to 1 every month except the current month I want to prorate it over the number of current days * calendar days.
For example :
the total is equal to 6 so I want to have 5.83.
this is my measure :
test =
VAR SelectedMonth = value(MAX('Calendrier'[l_month]))
VAR SelectedYear = value(MAX('Calendrier'[l_year]))
VAR IsCurrentMonthYear = SelectedMonth = MONTH(TODAY()) && SelectedYear = YEAR(TODAY())
VAR QteProrata = SUMX(
VALUES('Facturation_Ordre'[tariff_item_name]),
IF(
SelectedMonth = MONTH(TODAY()) && SelectedYear = YEAR(TODAY()),
DIVIDE([Qté_Prorata_Month], [Nbr_Jours_Calendaires]) * DAY(TODAY()),
[Qté_Prorata_Month]
)
)
RETURN
IF(
SelectedMonth = MONTH(TODAY()) && SelectedYear = YEAR(TODAY()),
SUMX(
SUMMARIZE(
'Facturation_Ordre',
'Facturation_Ordre'[tariff_item_name],
"TotalAmount",
SUMX(
FILTER('Facturation_Ordre', 'Facturation_Ordre'[tariff_item_name] = EARLIER('Facturation_Ordre'[tariff_item_name])),
DIVIDE(SUMX(VALUES('Facturation_Ordre'[tariff_item_name]), [Qté_Prorata_Month]), [Nbr_Jours_Calendaires]) * DAY(TODAY())
)),
[TotalAmount]
),[Qte_Prorata_Month])
for info :
[Qte_Prorata_Month] =
VAR NbrSemaine = [Nbr Semaine]
VAR nature = MAX('Facturation_Ordre'[nature])
VAR IsMonthLevel = NOT ISFILTERED('HU_all'[Num_Week])
RETURN
IF(
IsMonthLevel,
SUMX(VALUES('Facturation_Ordre'[tariff_item_name]), [TotalQte]),
IF(
nature = "Fixe",
DIVIDE(SUMX(VALUES('Facturation_Ordre'[tariff_item_name]), [TotalQte]), NbrSemaine),
SUMX(VALUES('Facturation_Ordre'[tariff_item_name]), [TotalQte])
)
)
thank you for your help
1 Reply
Hi, SARAHCH
Are you able to share the pbix file or data table without sensitive data for testing purposes.Best Regards,
Yang
Community Support Team
