Forum Discussion
USERELATIONSHIP
Hi reiskleiton ,
Looking at the data you have given beliw you can do it adding the following measure:
Excesso de limite = CALCULATE(SUM(CAR[Montante]); CAR[Cliente] in VALUES('Ordem Bloq'[Cliente]))Check result in attach PBIX.
Hi MFelix
I tried to explain how it should work.
It could be done in Power Query by Merge (Right Outer) CAR[Cliente] = Ordem Bloq[Cliente] AND Calendário [Mês e Ano], but I want to avoid to create a unecessary table to my model.
Thank you for your help.
Hi reiskleiton ,
Can you please explain how the calculation for the last column is done.
If I look I have the following data:
April - 3 customers in CAR with a total of 300 but only 1 cliente in Bloqueado on that date with the value of 200.
June - 3 customers in CAR with a total of 300 but 5 cliente in Bloqueado on that date with the value of 1000. your total value is 100
Can you give a little bit more detail please.
Hi MFelix
Sorry, I made a mistake.
You got it right.
April - 100 (1 cliente from CAR)May - 200 (2 clientes from CAR)
June - 300 (3 clientes from CAR)
Please, remember that the Excesso Limite is SUMX(
FILTER(CAR;CAR[Cliente] =Ordem Bloq[Cliente]);
CAR[Montante]
)
Do you need more information?
Thank you.
Hi reiskleiton ,
Sorry for asking again, but can you break out the calcultion you are using? I understand the DAX code you place in but how is the result if you do it on a piece of paper?
What are the values you are considering for getting those values.
Hi MFelix
Please, feel free to ask many questions you want.
Let me try do explain how I would do it by Power Query.
Here are the tables used
- CAR - Group by [Mês e Ano] and [Cliente] and SUM [Montante]
- Ordens Bloq - Group by [Mês e Ano] and [Cliente] and SUM [Valor da Ordem]
- Add both to the visualization table
- Then Merge (Right Outer) CAR with Ordem Bloq
CAR[Cliente] = Ordem Bloq[Cliente]
CAR[Mês e Ano] = Ordem Bloq[Mês e Ano] - After the Merge, I will keep all the Non Blank values from CAR[Montante] and add it to the visualization.
Hope it helps you.
- CAR - Group by [Mês e Ano] and [Cliente] and SUM [Montante]